Created
April 29, 2022 17:56
-
-
Save sjtalkar/fe84dc1c5e64a62fe5ba55afd8510cbf to your computer and use it in GitHub Desktop.
Dynamically grouping for user input and Excel Formula
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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