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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
JLambs20
Helper III
Helper III

Help with Power Query Formula Efficiency

Hello!

 

I'm looking to create a conditional column that is based on a different column within a range of cells.  

 

If the "Status" column contains the word "Finalized" for any of the cells in a range that all have the same value in a separate column (Number), then return "Finalized", if not "Current".  

 

Here is an example of what I mean

 

JLambs20_0-1675298147278.png

 

 

So, for Number 123456, there is one cell in the "Status" column that says "Finalized".  Since that's the case, the new column would say "Finalized" for each of those cells in the row with the same Number.  If the Status column doesn't say "Finalized", then it would say "Current". 

I currently have a solution for this issue in Power Query, HOWEVER, it is very slow. My data set isn't overly large (~10K rows) but my particular solution makes it feel like i'm working with hundreds of thousands of rows.  

Here is the currenty Power Query code:
Table.AddColumn(#"Removed Columns2", "Desired Output", each List.Min(Table.SelectRows(#"Changed Type", (x)=>x[Number]=[Number])[Status]))

 

Is there a better way to write the code so that it is more efficient/faster? Any help would be greatly appreciated!

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

Would you be Ok with a calculated column formula solution using the DAX language instead?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
JLambs20
Helper III
Helper III

Thank you for that feedback and I'll have a look into the youtube video.  In the meantime, can you tell me what this part of the query "(x)=>x" actually means/does? I got the formula from someone else who was assisting but didn't explain how it was working.  I'm more curious than anything. 

Thanks again.

amitchandak
Super User
Super User

@JLambs20 , to me it seems like a good option. other might be costly too

 

The other one is to create a separate group by the table with max value and merge it again

 

Modification to this nested index - https://www.youtube.com/watch?v=7CqXdSEN2k4

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.