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 in input table(in each month again). I couldn't find a way to build it. Can this be done with formula or VBA? Thanks in advance.

My input table looks like this:

Item	jan	feb	march	april	may	jun	jul	aug	sep	oct	nov	dec
Item1	1	2	2	3	4	2	3	4	4	5	6	6
Item2	2	3	3	1	2	2	1	2	1	3	5	6
Item3	5	6	5	4	3	3	3	2	3	4	4	5

And as an output I need this:

(Example: For Item2 in march, Three instances of Item2 is needed. So in march column, Three rows of Item2 should be 1 and the rest of Item2 rows should be 0)

Item	jan	feb	march	april	may	jun	jul	aug	sep	oct	nov	dec
Item1	1	1	1	1	1	1	1	1	1	1	1	1
Item1	0	1	1	1	1	1	1	1	1	1	1	1
Item1	0	0	0	1	1	0	1	1	1	1	1	1
Item1	0	0	0	0	1	0	0	1	1	1	1	1
Item1	0	0	0	0	0	0	0	0	0	1	1	1
Item1	0	0	0	0	0	0	0	0	0	0	1	1
Item2	1	1	1	1	1	1	1	1	1	1	1	1
Item2	1	1	1	0	1	1	0	1	0	1	1	1
Item2	0	1	1	0	0	0	0	0	0	1	1	1
Item2	0	0	0	0	0	0	0	0	0	0	1	1
Item2	0	0	0	0	0	0	0	0	0	0	1	1
Item2	0	0	0	0	0	0	0	0	0	0	0	1
Item3	1	1	1	1	1	1	1	1	1	1	1	1
Item3	1	1	1	1	1	1	1	1	1	1	1	1
Item3	1	1	1	1	1	1	1	0	1	1	1	1
Item3	1	1	1	1	0	0	0	0	0	1	1	1
Item3	1	1	1	0	0	0	0	0	0	0	0	1
Item3	0	1	0	0	0	0	0	0	0	0	0	0

Excel

Formulas and Functions

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

PQ_Canerik92.xlsx32 KB

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) )

Lorenzo

Silver Contributor

Jun 28, 2022

Hi Canerik92

With Power Query:

Query code:

let
    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    MaxByItem = Table.AddColumn(Source, "ItemMax", each
        List.Max(List.Skip(Record.FieldValues(_))), Int64.Type
    ),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(MaxByItem,
        {"Item", "ItemMax"}, "Month", "Value"
    ),
    ListOneZero = Table.AddColumn(UnpivotedOtherColumns, "ZeroOne",
        (x)=> List.Transform({1..x[ItemMax]},
            (y)=> if y <= x[Value] then 1 else 0),
        type list
    ),
    SelectedColumns = Table.SelectColumns(ListOneZero, {"Item", "Month", "ZeroOne"}),
    ExpandedZeroOne = Table.ExpandListColumn(SelectedColumns, "ZeroOne"),
    AddedGroupIndex = Table.Group(ExpandedZeroOne, {"Item","Month"},
        {"ItemData", each Table.AddIndexColumn(_,"Idx",0,1), type table}
    ),
    CombinedTables = Table.Combine(AddedGroupIndex[ItemData]),
    PivotedMonth = Table.Pivot(CombinedTables,
        List.Distinct(CombinedTables[Month]), "Month", "ZeroOne"
    ),
    RemovedIndex = Table.RemoveColumns(PivotedMonth,{"Idx"})
in
    RemovedIndex

PQ_Canerik92.xlsx28 KB

Lorenzo

Silver Contributor

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

PQ_Canerik92.xlsx32 KB

Canerik92
Copper Contributor
Jun 29, 2022
Many thanks L z.

Both of your solutions ran perfectly.

Have a nice day 🙂
- Lorenzo
  Silver Contributor
  Jun 29, 2022
  Glad I could help. Thanks for providing feedback & nice day too

Lorenzo
Silver Contributor
Jun 29, 2022
Hi Canerik92
I put together the following 3rd option:
let // Function BEGIN PivotOneZero = (values as list, names as list) as table => let MaxFromValues = List.Max(values), RepeatOneZero = List.Transform(values, each if _ < MaxFromValues then List.Repeat({1}, _) & List.Repeat({0}, MaxFromValues-_) else List.Repeat({1}, MaxFromValues) ) in Table.FromColumns(RepeatOneZero, names), // Function END Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content], Months = List.Skip(Table.ColumnNames(Source)), ValuesList = Table.AddColumn(Source, "ValuesList", each Record.ToList(Record.SelectFields(_,Months)), type list ), SelectedColumns = Table.SelectColumns(ValuesList, {"Item", "ValuesList"}), TableOneZero = Table.AddColumn(SelectedColumns, "OneZero", each PivotOneZero([ValuesList], Months), type table ), RemovedValuesList = Table.SelectColumns(TableOneZero,{"Item", "OneZero"}), ExpandedOneZero = Table.ExpandTableColumn(RemovedValuesList, "OneZero", Months) in ExpandedOneZero
Then expanded the Inputs table to 500 rows with random values between 3 and 100. This generates an Output table of 46500+ rows. Finaly refreshed the 3 queries 3 times

==> Option1 is - with this scenario - the slowest one, the 2 others compare
Hope this helps
PQ_Canerik92.xlsx5.9 MB
- PeterBartholomew1
  Silver Contributor
  Jun 30, 2022
  Lorenzo
  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.
  ExpandedIntegerTable.xlsx278 KB
  - Patrick2788
    Silver Contributor
    Jul 02, 2022
    PeterBartholomew1
    I revisited this task because I had gotten off track and you mentioned using the new re-shaping functions would be key to a solution.
    
    (This is going off the sample data offered by Canerik92 )
    
    The way I put this together, I see three pieces: the header, the 'row labels', and the matrix of 1s and 0s.
    
    For the row labels (StackMax=6 for this example). I chose each of the 3 rows 6 times with some sequencing:
    =CHOOSEROWS(item,INT(SEQUENCE(ROWS(item)*StackMax,1,1,1/StackMax)))
    For the header: A simple named item referring to the top row.
    
    For the matrix, I first reduced it to a scalar and used EXPAND to help pad with 0s.
    =REDUCE("",TOCOL(rng,,1),LAMBDA(a,v,TEXTJOIN(",",1,a,EXPAND(SEQUENCE(,v,1,0),1,StackMax,0))))
    
    Then split it and re-shaped it:
    =WRAPCOLS(TEXTSPLIT(String,,","),StackMax*ROWS(item))
    
    A bit of stacking to complete it:
    =VSTACK(months,HSTACK(ItemList,ReshapedArray))
    
    UnStack.xlsx11 KB

Forum Discussion

How to build a destacked table

Share

Resources