Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
Home
#
How to build a destacked table

- Home
- Microsoft Excel
- Excel
- How to build a destacked table

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 28 2022 05:15 AM

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 |

Labels:

10 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 28 2022 03:19 PM

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

best response confirmed by
Canerik92* (New Contributor)*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 28 2022 10:48 PM

Solution

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 29 2022 02:50 AM

Many thanks L z.

Both of your solutions ran perfectly.

Have a nice day

Both of your solutions ran perfectly.

Have a nice day

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 29 2022 03:13 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 29 2022 10:20 AM

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

==> Option1 is - with this scenario - the slowest one, the 2 others compare

Hope this helps

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 30 2022 05:39 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 30 2022 06:13 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jun 30 2022 06:46 AM

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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 01 2022 11:03 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Jul 02 2022 02:00 PM

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