Rank over partition multiple columns in Power Query NOT DAX!

[patoduck]

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? 🙂

[ImkeF · Accepted 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.

[Anonymous]

Hi,

Will you be displaying this data on report or you want it as a table in the report?

Thanks.

[patoduck]

It will be a dimension in the data model.

[Anonymous]

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.

[patoduck]

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.

---

[Anonymous]

Oh.Apologies. I missed it.

Can you check if the below video is what you need?

https://www.youtube.com/watch?v=Y7paK0yS5ic

Thanks.