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 ...
excel
Formulas and Functions
Macros and VBA
  • Lorenzo's avatar
    Lorenzo
    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
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 30, 2022

Canerik92 

There are currently some relevant array shaping functions on beta release in 365.  I defined a Lambda function 'Expandλ' to convert a single integer value into a row array of 1s

Expandλ
= LAMBDA(v,
    LET(
        k, SEQUENCE(1,6),
        SIGN(k<=v)
    )
  )

The worksheet formula is then

= LET(
  n, ROWS(data),
  colData,   TOCOL(data,,1),
  expanded,  TOCOL(Expandλ(colData)),
  WRAPCOLS(expanded,6*n)
  )

Resources