Search code examples
powerbidaxpowerquerypower-bi-report-server

For each different row on a column, keep only the top N row

I'm trying to aggregate the rows on this table. I want to eliminate duplicated bundle id's and keep only the most installed app name for each bundle ID. As you can see on the image, there are several different bundle id's right now

enter image description here

I tried to group by it, however if I group by bundle ID only it works but the app name doesn't appear. I want to remove duplicates for bundle ID'S and keep the app name with most installations (count) so I would like something like this comparing to the image:

Bundle ID app name count .apps.Photos photos 3,530

Solution

  • PowerBI/PowerQuery using M

    It is unclear if you want the sum of the App_Bundle counts, or the count of just the largest App_Name in each Bundle, but here is both

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"App_Bundle_ID"}, {{"data", each Table.FirstN(Table.Sort(_,{{"Count", Order.Descending}}),1), type table [App_Bundle_ID=text, App_Name=text, Count=number]}, {"MasterCount", each List.Sum([Count]), type number}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"App_Name", "Count"}, {"App_Name", "Count"})
in  #"Expanded data"

    enter image description here

    if you dont want the count column then use

    #"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"App_Name"}, {"App_Name"})