Forum Discussion
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 |
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
10 Replies
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) )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 ExpandedOneZeroThen 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
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.
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))
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 ExpandedItemTablesHi 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
