Forum Discussion

patoduck's avatar
patoduck
Icon for Helper III rankHelper III
7 years ago
Solved

Rank over partition multiple columns in Power Query NOT DAX!

I have the following table 

documenttopicgamma
110.2890625
120.2578125
130.2265625
140.2265625
210.2358491
220.2547170
230.2358491
240.273584

 

And I need to return  only the topic with the highest gamma per document so it will look like this

documenttopicgamma
110.2890625
240.273584

 

In case of ties really doesnt matter, so it will be like a row_number over partition in SQL.

ImkeFpiece of cake? :-)

 

Need Help
  • ImkeF's avatar
    ImkeF
    7 years ago

    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.

     

     

6 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi,

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

    Thanks.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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.