Problem recreating process normally done in excel

[Anonymous]

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:

Item	GI Qty	Midstep	D1	D2	D3	M3
70289 *GT21 FENCE BOARD TREATED 22X144 4.8MT	90	22X144	0.022	0.144	4.8	1.368576
11019 *GT33 FENCE POSTS WEATHERED TREATED 100X100 1.8MT	115	100X100	0.1	0.1	1.8	2.07
19675 47X60 SAWN TREATED 3.6M	199	47X60	0.047	0.06	3.6	2.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

[Greg_Deckler]

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

[Anonymous]

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?

[Greg_Deckler · Accepted solution]

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

[Anonymous]

Thanks Greg you are a star. This was the issue 🙂