Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.