cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mgallagher Frequent Visitor
Frequent Visitor

Problem recreating process normally done in excel

Hello,

 

I'm trying to use power query/Power BI to automate a process i do in excel manually/with formula - i'm only new to using power query and M language and my problem is possibly also trying to exactly replicate my excel process (same number of columns etc) and using formula that don't exist/are different in Power query.

 

Rather than show the whole current process (as you might easily think of a more elegant solution ) essentially my task is create a column that will calculate m3 volume. There is a column of item names and a column of quantities. For example:

 

ItemGI QtyMidstepD1D2D3M3
70289 *GT21 FENCE BOARD TREATED 22X144 4.8MT90 22X1440.0220.1444.81.368576
11019 *GT33 FENCE POSTS WEATHERED TREATED 100X100 1.8MT115100X1000.10.11.82.07
19675 47X60 SAWN TREATED 3.6M199 47X60 0.0470.063.62.020248


Only the first 2 columns exist in the original data sheet. The final 5 are added columns i've used to try to get to my M3 calculation. Formulas used for final 5 columns:

 

Formula Used
=MID(D24,SEARCH("X",D24)-3,7)
=LEFT(F24,SEARCH("X",F24)-1)/1000
=RIGHT(F24,LEN(F24)-SEARCH("x",F24))/1000
=MID(D24,SEARCH(".?M",D24)-1,3)
=E24*G24*H24*I24


How can i do this in power query - when i try to search for 'X' for example it doesn't work/syntax is wrong? Apologies if i'm asking a stupid question. 

 

Many Thanks,

 

Marcus

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Problem recreating process normally done in excel

There is an extra optional parameter for SEARCH that is the result to return if not found. https://docs.microsoft.com/en-us/dax/search-function-dax


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


4 REPLIES 4
Super User
Super User

Re: Problem recreating process normally done in excel

If you can do this in DAX instead of Power Query, the formulas should actually be 100% correct or very close to it.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


mgallagher Frequent Visitor
Frequent Visitor

Re: Problem recreating process normally done in excel

Well the problem i was having was that it was saying the value did not exist when i tried to use the Search function - which i believed to be the same in both?

 

it says "Calculation error in column 'Sheet1'[]: The search Text provided to function 'SEARCH' could not be found in the given text."

 

Does it use a different wildcard rather than the "X" in the excel formula?

 

 

 

 

Super User
Super User

Re: Problem recreating process normally done in excel

There is an extra optional parameter for SEARCH that is the result to return if not found. https://docs.microsoft.com/en-us/dax/search-function-dax


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
mgallagher Frequent Visitor
Frequent Visitor

Re: Problem recreating process normally done in excel

Thanks Greg you are a star. This was the issue Smiley Happy