SOLVED

Iron Contributor

# 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

4 Replies
best response confirmed by anupambit1797 (Iron Contributor)
Solution

# Re: Power Query Issue

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

# Re: Power Query Issue

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

Br,

Anupam

# Re: Power Query Issue

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

# Re: Power Query Issue

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

1 best response

Accepted Solutions
best response confirmed by anupambit1797 (Iron Contributor)
Solution

# Re: Power Query Issue

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