Forum Discussion
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:
| 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
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
4 Replies
If you can do this in DAX instead of Power Query, the formulas should actually be 100% correct or very close to it.
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?
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