Keep top row of each category in a table subgroup based on a metric columns ranking

[StephenF]

Question: How can I transform a table of data that includes a content ID, page name, and page views, in such a way that we only retain the page name associated with the highest page views for each distinct content ID?

Here's a representation of the data shared:

Content ID	Page Name	Page Views
2321312	accident	213
2321312	big accident	23
2321312	ouch	2
2321311	burgers	465643
2321387	feeling ill	2
2321387	feeling sick	2
23213127	cars	1212

The challenge is to transform this data into a format where each content ID is associated with only one page name, specifically the one with the highest number of page views. If there's a tie in page views, we need a strategy to break the tie and select only one page name. Rows with other page names should be removed. 4 rows in the output in this example.

[Anonymous · Accepted solution]

Hi @StephenF ,

Please try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5LDoAgDESvQli7oeLnLoQFVsRGognK/S2ukN3kvc6kxkjoQfUKZCcdIq3+fDgykrar5UJB1AetvzLuhVdYlVpOwaebkx6HUVeteWK4eR/pDIJi/Jd/9iY8mm0oHt23rIA/sPYF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Content ID" = _t, #"Page Name" = _t, #"Page Views" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Content ID", Int64.Type}, {"Page Name", type text}, {"Page Views", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(Table.Group(#"Changed Type", {"Content ID"}, {{"Max Page Views", each List.Max([Page Views])}}),(x) => x[Content ID] = [Content ID] and x[Max Page Views] = [Page Views])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Max Page Views"}, {"Max Page Views"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Max Page Views] <> null)),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Content ID"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Max Page Views"})
in
    #"Removed Columns"

 Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum