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 29, 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
Lorenzo
Jun 29, 2022Silver Contributor
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