cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

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

 

1 ACCEPTED SOLUTION
Super User II
Super User II

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

View solution in original post

6 REPLIES 6
Super User II
Super User II

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

View solution in original post

Solution Supplier
Solution Supplier

Hi,

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

Thanks.

It will be a dimension in the data model.

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.


@vaibhavdesai 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.



@vaibhavdesai 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.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors