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
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
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!
Hi,
Would you be Ok with a calculated column formula solution using the DAX language instead?
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.
@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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
108 | |
90 | |
61 |
User | Count |
---|---|
171 | |
138 | |
132 | |
102 | |
86 |