Forum Discussion
Create table using LET based on column values
This will do it. The function is made specifically for this arrangement. Essentially, this is doing what PowerQuery would be doing with changing the order of columns and then unpivoting.
Thanks again Patrick. This works perfectly. Silly question but should I want to include another column from the initial table, is it just a question of updating the "matrix" range from say $A$2:$U$16 to say $A$2:$V$16?
Yes, that will work. Tested it here.
Hi Patrick, I get an error when I expand the range. I updated the "kpis_table" range to include column 11
=LAMBDA(kpis_table, LET(rows, DROP(CHOOSECOLS(kpis_table, 1, 2, 4, 3, 6, 7, 11), 1)
I want column 11 to be the last row displayed in the output, what else do I change in the formula, bit confused. I added the header too, just I get "N/A" as values
If the 11th column is a value column (similar to the "T" columns), there's no need to specify it in function. If this 11th column is to server as a "row label" then function would need to be updated because presently it presumes the value columns start at 8 until the end. It would be do-able but we'd have to pick through the values columns instead of simply dropping the first 7.