Forum Discussion
Solved
Power Query Issue
Dear Experts, I have a Table as in the "Input" Sheet, and want to make a Table as in the "Output" Sheet. as below:- Could you please help share the power query fun...
=INDEX(Input!A$2:A$148,SMALL(IF(Input!A$2:A$148<>"",ROW($A$2:$A$148)-1),ROW($A1)))
In legacy Excel you can apply this formula which is in cell A27 in the output sheet and filled across range A27:G47. In legacy Excel e.g. Excel 2013 we have to enter the formula as an arrayformula with ctrl+shift+enter.
With 365 it's straight forward. Determine number of columns in the table, convert table to a vector to remove blanks, re-wrap with WRAPROWS.
=LET(
c, COLUMNS(Table2),
vector, TOCOL(Table2, 1),
WRAPROWS(vector, c)
)