Current query:
Code: Select all
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Delegate System", type text}, {"Item No.", type text}, {"Item", type text}, {"Vendor Group", type text}, {"Vendor", type text}, {"Over Group", type text}, {"Major Group", type text}, {"Item Group", type text}, {"Cost Center/Store", type text}, {"Unit", type text}, {"ANNUAL QTY/UNIT", Int64.Type}, {"MANUFACTURER", type any}, {"REPLACE/KEEP COMMENTS", type any}, {"STATE", type text}, {"Abco Code", Int64.Type}, {"Desc", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Delegate System", "Item", "Vendor Group", "Vendor", "Over Group", "Major Group", "Item Group", "Cost Center/Store", "Unit", "REPLACE/KEEP COMMENTS", "STATE"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Abco Code", "Desc", "Item No.", "ANNUAL QTY/UNIT", "MANUFACTURER"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"MANUFACTURER"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"Abco Code", "Desc", "ANNUAL QTY/UNIT", "Item No."}),
#"Grouped Rows" = Table.Group(#"Reordered Columns", {"Abco Code", "Desc"}, {{"Total_Qty", each List.Sum([#"ANNUAL QTY/UNIT"]), type nullable number}, {"Old_Codes", each Text.Combine(List.Distinct([#"Item No."]), ", "), type text}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Total_Qty", Order.Descending}})
in
#"Sorted Rows"