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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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