Power Query Issue

[anupambit1797]

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

[#2 · Accepted solution]

@anupambit1797 

=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.

[#3]

Thank you very much@OliverScheurich , also is it possible to achieve this via PQ?

Br,

Anupam

[#4]

@anupambit1797 

You are welcome. See the green result table in the output sheet of the attached file.

[#5]

@anupambit1797 

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)
)