Skip to main content
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.

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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


Power BI Monthly Update - May 2024

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


Fabric certifications survey

Certification feedback opportunity for the community.