Forum Discussion
Create table using LET based on column values
Hi all
Hope you can help.
I am trying to create an output table based on another tables column data.
Example:
Original Data Table:
Task Target Group 1 Group 2 Group 3 Group 4
Task 1 10 5 10
Task 2 20 10
Task 3 10 5 15 16
Task 4 50 90 10 20 19
Output Table Desired:
Tasks Team Result
Task 1 Group 1 5
Task 1 Group 3 10
Task 2 Group 2 10
Task 3 Group 1 5
Task 3 Group 2 15
Task 3 Group 3 16
Task 4 Group 1 90
Task 4 Group 2 10
Task 4 Group 3 20
Task 4 Group 4 19
Any advice where to start please?
Thanks
11 Replies
Another basic unpivot option:
=LET( v, H3:U16, t, v<>"", λ, LAMBDA(x,TOCOL(IFS(t,x),2)), a, INDEX(A3:G16,λ(SEQUENCE(ROWS(v))),{1,2,4,3,6,7}), HSTACK(TAKE(a,,4),λ(H2:U2),DROP(a,,4),λ(v)) )Adjust the 3 range references as needed. See attached...
This Lambda will unpivot a matrix by specifying rows, columns, and values. Once the data is flat, you can filter as needed.
UnPivotMλ = LAMBDA(rows, columns, values, LET( i, ROWS(rows), j, COLUMNS(rows), c, COLUMNS(columns), s, TOCOL(SEQUENCE(, j) * SEQUENCE(c, , 1, 0)), row_labels, WRAPROWS(TOCOL(CHOOSECOLS(rows, s)), j), attribute, TOCOL(CHOOSEROWS(columns, SEQUENCE(i, , 1, 0))), v, TOCOL(values), HSTACK(row_labels, attribute, v) ) );It's a good idea to study the PQ solution Riny_van_Eekelen provided because it's an essential skill.
Thank you for your reply and assistance!
Sadly Power Query & Macros is blocked in my work organisation so I am restricted to LET.
When implementing your example, I get a few #N/As on columns and also the issue where if the value is blank, a 0 is being displayed.
Can this formula be modified to work on the following logic?
If the value is empty, the whole row is not repeated?
For example these highlighted would not be displayed and the overall order would still align.
The attached removes the rows with 0s.
For the columns, the width must match the width of the selected values (even if the columns are blank). If your example presents a differently arranged header then a sample may be needed.
I would prefer to solve this with Power Query as demonstrated in the attached file.
