Transposing issue

[Anonymous]

Hi

I have data in multiple sheets in multiple files and the data is arranged thus:

Sheets 1-50

col1            col2          

header1     1

header2     2

header3     3

...but when I power query it, the data ends up alongside each other rather than stacking...

I want it to transpose to look like:

header1     header2     header3

1                2                3

Nice and easy... but when I combine the tabs and transpose, the data ends up alongside each other:

header1     header2     header3     header1     header2     header3     header1     header2     header3

1                2                3                1                2                3                 1                2                3

What am I doing wrong?

[v-yingjl · Accepted solution]

Hi @Anonymous ,

You can create a custom function like this:

let
    Source = (tab as table, x) => Table.SelectRows(tab, each [col1] = x)[col2]{0}
in
    Source

Invoke this function in Table1 for Table2, Table3..., then you can transpose the table and use the first row as header

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[watkinnc]

You should add a column of null values, then place it at the first colun position, and then tranpose:

= Table.AddColumn(#"Name of Table or Last Step Name", "Nulls", each null)

Now your transpose will work, and you can then Promote Headers.

--Nate