Forum Discussion

Canerik92's avatar
Canerik92
Copper Contributor
Jun 28, 2022
Solved

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 ...
  • Lorenzo's avatar
    Jun 29, 2022

    Canerik92 

     

    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

Resources