SOLVED

How to build a destacked table

New Contributor

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:

 

Itemjanfebmarchaprilmayjunjulaugsepoctnovdec
Item1122342344566
Item2233122121356
Item3565433323445

 

 

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)

 

Itemjanfebmarchaprilmayjunjulaugsepoctnovdec
Item1111111111111
Item1011111111111
Item1000110111111
Item1000010011111
Item1000000000111
Item1000000000011
Item2111111111111
Item2111011010111
Item2011000000111
Item2000000000011
Item2000000000011
Item2000000000001
Item3111111111111
Item3111111111111
Item3111111101111
Item3111100000111
Item3111000000001
Item3010000000000

 

 

10 Replies

Hi @Canerik92 

 

With Power Query:

_Screenshot.png

 

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

 

best response confirmed by Canerik92 (New Contributor)
Solution

@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
Many thanks L z.

Both of your solutions ran perfectly.

Have a nice day
Glad I could help. Thanks for providing feedback & nice day too

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

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

@L z. 

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.

@Peter Bartholomew 

 

Thanks for sharing your LAMDBA & run time. TBH I don't measure as precisely as you do (just watched PQ Refresh window in this case)

BTW @Peter Bartholomew 

What tools do you use/recommend to measure formulas exec. time? Something more sophisticated than the VBA suggested on Improving calculation performance? Thanks

@Peter Bartholomew 

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