Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper III
Helper III

Help with Power Query Formula Efficiency



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





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!

Super User
Super User


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

Ashish Mathur
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.

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 -

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors