Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have the following table
document | topic | gamma |
1 | 1 | 0.2890625 |
1 | 2 | 0.2578125 |
1 | 3 | 0.2265625 |
1 | 4 | 0.2265625 |
2 | 1 | 0.2358491 |
2 | 2 | 0.2547170 |
2 | 3 | 0.2358491 |
2 | 4 | 0.273584 |
And I need to return only the topic with the highest gamma per document so it will look like this
document | topic | gamma |
1 | 1 | 0.2890625 |
2 | 4 | 0.273584 |
In case of ties really doesnt matter, so it will be like a row_number over partition in SQL.
@ImkeFpiece of cake? 🙂
Solved! Go to Solution.
Hi @patoduck ,
yes, please try the following code (paste into the advanced editor and check what it does):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMDPSMLSwMzI1OlWB2IqBFE1NTcwhBJ1BgiamRmiqzWBEPUCGGusamFiaUhXBRmrom5oTlc0BirUqix5iBhpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [document = _t, topic = _t, gamma = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"document", Int64.Type}, {"topic", Int64.Type}, {"gamma", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"document"}, {{"All", each Table.SelectRows(_, (x) => (x[gamma] = List.Max(_[gamma]))){0}}}), #"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"topic", "gamma"}, {"topic", "gamma"}) in #"Expanded All"
If your raw data has all rows for one document in a sequence, you can even use GroupKind.Local to speed up the calculation considerably:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMDPSMLSwMzI1OlWB2IqBFE1NTcwhBJ1BgiamRmiqzWBEPUCGGusamFiaUhXBRmrom5oTlc0BirUqix5iBhpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [document = _t, topic = _t, gamma = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"document", Int64.Type}, {"topic", Int64.Type}, {"gamma", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"document"}, {{"All", each Table.SelectRows(_, (x) => (x[gamma] = List.Max(_[gamma]))){0}}}, GroupKind.Local), #"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"topic", "gamma"}, {"topic", "gamma"}) in #"Expanded All"
But this only works if they are all nicely in one sequence each.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @patoduck ,
yes, please try the following code (paste into the advanced editor and check what it does):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMDPSMLSwMzI1OlWB2IqBFE1NTcwhBJ1BgiamRmiqzWBEPUCGGusamFiaUhXBRmrom5oTlc0BirUqix5iBhpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [document = _t, topic = _t, gamma = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"document", Int64.Type}, {"topic", Int64.Type}, {"gamma", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"document"}, {{"All", each Table.SelectRows(_, (x) => (x[gamma] = List.Max(_[gamma]))){0}}}), #"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"topic", "gamma"}, {"topic", "gamma"}) in #"Expanded All"
If your raw data has all rows for one document in a sequence, you can even use GroupKind.Local to speed up the calculation considerably:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMDPSMLSwMzI1OlWB2IqBFE1NTcwhBJ1BgiamRmiqzWBEPUCGGusamFiaUhXBRmrom5oTlc0BirUqix5iBhpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [document = _t, topic = _t, gamma = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"document", Int64.Type}, {"topic", Int64.Type}, {"gamma", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"document"}, {{"All", each Table.SelectRows(_, (x) => (x[gamma] = List.Max(_[gamma]))){0}}}, GroupKind.Local), #"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"topic", "gamma"}, {"topic", "gamma"}) in #"Expanded All"
But this only works if they are all nicely in one sequence each.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi,
Will you be displaying this data on report or you want it as a table in the report?
Thanks.
Hi,
Try using the below DAX to create the new table from the existing table.
In the below example 'Data' is the name of my existing table and 'Filtered Data' is the new derived dimension.
Filtered Data = FILTER(Data, RANKX( FILTER( Data, Data[document]=EARLIER(Data[document]) ), Data[gamma] ) == 1 )
Thanks.
Thanks, but I need a Power Query, ETL side solution, not DAX as this will affect performance, table contains a large number of rows.
Just to clarify DAX and M language (Power Query) are different languages , therefore the title in my question.
@Anonymous wrote:Hi,
Try using the below DAX to create the new table from the existing table.
In the below example 'Data' is the name of my existing table and 'Filtered Data' is the new derived dimension.
Filtered Data = FILTER(Data, RANKX( FILTER( Data, Data[document]=EARLIER(Data[document]) ), Data[gamma] ) == 1 )Thanks.
@Anonymous wrote:Hi,
Try using the below DAX to create the new table from the existing table.
In the below example 'Data' is the name of my existing table and 'Filtered Data' is the new derived dimension.
Filtered Data = FILTER(Data, RANKX( FILTER( Data, Data[document]=EARLIER(Data[document]) ), Data[gamma] ) == 1 )Thanks.
Oh.Apologies. I missed it.
Can you check if the below video is what you need?
https://www.youtube.com/watch?v=Y7paK0yS5ic
Thanks.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
73 | |
72 | |
37 | |
31 | |
26 |
User | Count |
---|---|
95 | |
50 | |
43 | |
40 | |
35 |