Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
Thanks Greg you are a star. This was the issue 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |