Skip to content

Instantly share code, notes, and snippets.

@sjtalkar
Created April 29, 2022 17:56
Show Gist options
  • Save sjtalkar/fe84dc1c5e64a62fe5ba55afd8510cbf to your computer and use it in GitHub Desktop.
Save sjtalkar/fe84dc1c5e64a62fe5ba55afd8510cbf to your computer and use it in GitHub Desktop.
Dynamically grouping for user input and Excel Formula
let
Source = Excel.Workbook(File.Contents("G:\My Drive\Eric\DynamicPivot-Two products Six Suppliers.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
set_headers = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
set_types = Table.TransformColumnTypes(set_headers,{{"Product No", Int64.Type}, {"Supplier", type text}, {"Month", type text}, {"Amount", Int64.Type}}),
replace_null_amounts = Table.ReplaceValue(set_types,null,0,Replacer.ReplaceValue,{"Amount"}),
group_by_product_supplier_month = Table.Group(replace_null_amounts, {"Product No", "Supplier", "Month"}, {{"Total", each List.Sum([Amount]), type nullable number}}),
pivot_months = Table.Pivot(group_by_product_supplier_month, List.Distinct(group_by_product_supplier_month[Month]), "Month", "Total", List.Sum),
remove_space_from_column_name = Table.RenameColumns(pivot_months,{{"Product No", "Product_No"}}),
//At the end of each group (identified by Table.RowCount)add a table of rows that need to added for user input
group_by_product = Table.Group(remove_space_from_column_name, {"Product_No"}, {{"Count",
each
Table.InsertRows(_, Table.RowCount(_),
//List of rows to be added
{[Product_No="User Input",Supplier="",Apr="",May="",Jun="",Jul=""],
[Product_No="Additional User Input",Supplier="",Apr="",May="",Jun="",Jul=""]
}),
type table [Product_No=nullable number, Supplier=nullable text, Apr=nullable number, May=nullable number, Jun=nullable number, Jul=nullable number]}}),
expand_grouped_rows = Table.ExpandTableColumn(group_by_product, "Count", {"Product_No", "Supplier", "Apr", "May", "Jun", "Jul"}, {"Product_No.1", "Supplier", "Apr", "May", "Jun", "Jul"})
in
expand_grouped_rows
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment