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 |
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
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
Jun 29 2022 02:50 AM
Jun 29 2022 03:13 AM
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 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
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)
)
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.
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)
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
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))
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