Power Query Grouping Question

[Anonymous]

What grouping operation in power query could I do to pull out ONLY the supplier Key and leader with the most spend on an individual supplier key.

That supplier has 2 leaders on it. My goal is to make a table of ONLY supplier key and leader with most spend.

Mark Janecek

PTP Metrics Analyst 

Johnson Controls

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc4xCsAgDIXhu2R2MIlRexZxKNSh4CCW3r9mEYS6Pfj44aUEz9tavUu3CAZqOa/SdaGXANn8OqmLHKvTdFZHG3buxiIk3Lloz2JX5+leexFe3U0P2keUnUftA45/+QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, LeaderName = _t, Spend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"LeaderName", type text}, {"Spend", Int64.Type}}),
 #"Grouped rows" = Table.Group(Source, {"Key"}, {{"NiceTable", each Table.AddIndexColumn(Table.Sort(_,{{"Spend", Order.Descending}} ), "Index",1,1), type table}} ),
    #"Expanded NiceTable" = Table.ExpandTableColumn(#"Grouped rows", "NiceTable", {"LeaderName", "Spend", "Index"}, {"LeaderName", "Spend", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded NiceTable", each ([Index] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"

If the above one can't help you get the desired result, please provide some sample data in supplier info table and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak]

@Anonymous , refer if this can help

https://gorilla.bi/power-query/using-table-max/