Power Query + stack columns vertically

Question

I have

a	c	e
b	d	f

and I want

a
b
c
d
e
f

There could be more columns (and more rows) than showed above.

If possible I wish a Power Query solution.

Answer 1

Hans, don't forget to mark Lz's new reply as answer (and take the "answer" off of the earlier reply.

.

Excel allows you get the same result using many different paths.

.

Using Lz's reply as an inspiration (which I've done several times before), I came up with an alternative approach.

.

Load the source table into PowerQuery

Select all of the columns

Transform tab > Table group > Transpose command

Select all of the columns

Transform tab > Any Column group > Unpivot Columns drop down > Unpivot Columns command

Remove the "Attribute" column

.

.

Here is the resulting M Code:

.

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),

    #"Transposed Table" = Table.Transpose(#"Changed Type"),

    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {}, "Attribute", "Value"),

    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})

in

    #"Removed Columns"

.

.

I actually started by using the Transpose() function in Excel, then unpivotting that result, which lead me to try the final solution I described above.

.

Lz's answer keeps the blank cell, mine doesn't

If this order is important to you, then you should have some way of allowing Excel to sort to recreate it if you  use some other column to sort the data.  In this example I used PowerQuery to add an index column

Answer 2

Hi,

This M code works

let

    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text1", type text}, {"Text2", type text}, {"Text3", type text}}),

    #"Transposed Table" = Table.Transpose(#"Changed Type"),

    #"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 1, 1),

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),

    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Index", "Attribute"})

in

    #"Removed Columns"

Hope this helps.

Answer 3

Hey Rohn

took an absurdly long time > I know...

I did not expect to be part of the picture > Gil's pitfall article is way better than my suggestion

Re. Replace null in case we want to keep blank cells > I had that in mind, good you talked about it 

EDIT On more reflection... The initial Changed Type step is simply useless (good reason to remove it) as types are not preserved when the table is Transposed

Thanks