cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User III
Super User III

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
Highlighted
Solution Supplier
Solution Supplier

Hi,

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

Thanks.

Highlighted

It will be a dimension in the data model.
Highlighted

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.

Highlighted

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.


 

Highlighted

Oh.Apologies. I missed it.

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

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

Thanks.

Highlighted
Super User III
Super User III

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors