Forum Discussion
Canerik92
Jun 28, 2022Copper Contributor
How to build a destacked table
Hello, I would like to build a destacked table. My input table shows number of each item needed in each month. Output should give these same items in form of 1's and 0's for the number specified ...
- Jun 28, 2022
Another option
let Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content], UnpivotedOtherColumns = Table.UnpivotOtherColumns(Source, {"Item"}, "Month", "Value"), MaxByItem = Table.Group(UnpivotedOtherColumns, {"Item"}, { {"Data", each _, type table}, {"ItemMax", each List.Max([Value]), type number} } ), ListOneToMax = Table.AddColumn(MaxByItem, "OneToMax", each {1..[ItemMax]}, type list), ExpandedData = Table.ExpandTableColumn(ListOneToMax, "Data", {"Month", "Value"}), ListOneZero = Table.AddColumn(ExpandedData, "OneZero", (x)=> List.Transform(x[OneToMax], (y)=> if y <= x[Value] then 1 else 0 ), type list ), SelectedColumns = Table.SelectColumns(ListOneZero,{"Item", "Month", "OneZero"}), PivotedItemTable = Table.Group(SelectedColumns, {"Item"}, {"Data", each Table.FromColumns([OneZero], [Month]), type table} ), ExpandedItemTables = Table.ExpandTableColumn(PivotedItemTable, "Data", List.Skip(Table.ColumnNames(Source)) ) in ExpandedItemTables
PeterBartholomew1
Jun 30, 2022Silver Contributor
For me, the calculation with 500 rows and randomly-chosen values in the range 3-100 (50,000 output rows) took about 200ms.
Being based on a volatile array function, the price was paid at every worksheet change.
Lorenzo
Jul 01, 2022Silver Contributor
What tools do you use/recommend to measure formulas exec. time? Something more sophisticated than the VBA suggested on Improving calculation performance? Thanks