Forum Discussion
Solved
Transpose rows and join with row header using formula
Hi! I need to use a formula (not macro) to transpose a table and return another table based on the row header and each individual column value. Optionally a textjoin of the combination cold work. ...
That could be
=LET( rws, ROWS(Table1), cls, COLUMNS(Table1), k, SEQUENCE(rws*(cls-1)), i, MOD( k-1, cls-1)+2, j, INT( (k-1)/(cls-1)) +1, postfix, INDEX( Table1, j, i ), all, IF( ISBLANK(postfix), "", INDEX( Table1, j,1 ) & "-" & postfix), FILTER(all, all<>"") )
I haven’t used PowerQuery.
The intention is to distribute this as a template. I can’t have any inbuilt dependencies other than the version of Excel that is distributed with the E5 license.
Would this work with PowerQuery?
The intention is to distribute this as a template. I can’t have any inbuilt dependencies other than the version of Excel that is distributed with the E5 license.
Would this work with PowerQuery?
That could be
=LET(
rws, ROWS(Table1),
cls, COLUMNS(Table1),
k, SEQUENCE(rws*(cls-1)),
i, MOD( k-1, cls-1)+2,
j, INT( (k-1)/(cls-1)) +1,
postfix, INDEX( Table1, j, i ),
all, IF( ISBLANK(postfix), "", INDEX( Table1, j,1 ) & "-" & postfix),
FILTER(all, all<>"") )- Works like a charm! Thank you!
Now... could you please offer an explanation... 🙂Not sure which kind of explanations are required, it depends on how familiar you are with dynamic array functions.
First one is LET function (microsoft.com) which combines our calculations in one block.
SEQUENCE() generates list of indexes for data part of the table, i and j extracts from it column and row number related to each index.
With INDEX() we generate for every index some value depends on related column/row number, finally FILTER() all values with empty texts.