Use Power Query (M) to list the top 2 categories sold by a Sales Rep.

[mwaltercpa]

I found this M code on the forum (From Marcel Beug) the other night.. I used it to sum then listing the top category sold by each sales rep.  Wondering if there is a way to modify this code to list out the 'top 2' categories sold by each sales rep? 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc69CoAwDEbRd+ns4H91FJycBN3EIYKgoLWIDr69lYTaoNMXznBJ14lqm5TwRA3zM4Hvi96zWgw7mMkYtqBHMwnD5tR6ucwRITewUvUwG7pI0dg1akrXbDJHbTd1vZ/GDPmjhDxKaKspcgnqE0WjpnSNJ9FsUSb/xf4G", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SalesRep = _t, Category = _t, Amt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesRep", type text}, {"Category", type text}, {"Amt", Int64.Type}}),
   
  
    #"Changed Type2" = Table.TransformColumnTypes(
                                         #"Changed Type",{{"Category", type text},
                                                          {"SalesRep", type text},
                                                          {"Amt", Int64.Type}
                                                         }
                                       ),

 /*This step converts the column types to text and numbers*/

    #"Grouped Sum" = Table.Group(#"Changed Type2", {"SalesRep", "Category"}, {{"Amt", each List.Sum([Amt]), type number}}),
  
       GroupRecByName = Table.Group(#"Grouped Sum",
                                 {"SalesRep"},
                                 {
                                  {"AllRecords", each _, type table},
                                  {"MaxSales", each List.Max([Amt]), type number}
                                 }
                         ),
/*This step creates a table grouped by the column rep, then lists two new type Table columns, "AllData" Grouped by Rep & "MaxSales" lists max $.. Which intersect below to form a Custom Column that lists the Max row of Rep, Category, and max Sales within a table */

    SelctMaxRec = Table.AddColumn(GroupRecByName,
                                          "MaxRecord", (x) => Table.SelectRows(x[AllRecords],
                                                                            each [Amt] = x[MaxSales])
                                  ),
/*This step adds a col "MaxRecord" that filters AllRecords for the one that intersects the MaxSales amt*/
   

    RemovedColumns = Table.RemoveColumns(SelctMaxRec,{"AllRecords", "MaxSales"}),

/*Removes the temporary "AllData" and "MaxSales" */

    Expanded = Table.ExpandTableColumn(RemovedColumns, "MaxRecord", {"Category", "Amt"}, {"Category", "Amt"}),

/*Expands Columns*/

    Typed2 = Table.TransformColumnTypes(Expanded,{{"Category", type text}, {"Amt", type number}})

/*Changes type of Amt to Number*/

in
    Typed2

[Zubair_Muhammad · Accepted solution]

@mwaltercpa

try this
Basically idea is to

1)Group by each Sales rep

2) Sort the Rows for each Sales Rep by Amout

3) Add an Index Column for each Sales rep

4) Select the Index values 1 and 2 i.e Top2 Sales Rep

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc69CoAwDEbRd+ns4H91FJycBN3EIYKgoLWIDr69lYTaoNMXznBJ14lqm5TwRA3zM4Hvi96zWgw7mMkYtqBHMwnD5tR6ucwRITewUvUwG7pI0dg1akrXbDJHbTd1vZ/GDPmjhDxKaKspcgnqE0WjpnSNJ9FsUSb/xf4G", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SalesRep = _t, Category = _t, Amt = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SalesRep", type text}, {"Category", type text}, {"Amt", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"SalesRep"}, {{"All", each Table.AddIndexColumn(Table.Sort(_,{{"Amt", Order.Descending}}),"Index",1,1), type table}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Category", "Amt", "Index"}, {"Category", "Amt", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded All", each ([Index] = 1 or [Index] = 2)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"

[mwaltercpa]

Thanks so much!  This worked perfectly.  🙂