cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

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.


I have book! Learn Power BI from Packt


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


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 334 members 3,720 guests
Please welcome our newest community members: