Forum Discussion
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 function or the "M Query" or the Legacy Excel formulae which can achieve this?
Basically , deleting all the empty cells in the input and shifting those cells up can do the job, but now sure how to achieve this.
Thanks in Advance,
Br,
Anupam
=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.
=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.
You are welcome. See the green result table in the output sheet of the attached file.
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) )
