Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mwaltercpa
Advocate III
Advocate III

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

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
1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@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"

Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

@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"

Regards
Zubair

Please try my custom visuals

Thanks so much!  This worked perfectly.  🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.