Append contents of one column to another in Power Query

[datatronics505]

Hi,
I am stuck at appending contents of one column to another in Power Query. In the simplest of examples:

Column1	Column2
abc	def

should turn into:

Column1

abc

def

Again, in Power Query.

 

[jdellasala]

Please use XL2BB when posting data. It's a lot easier than having to manually type it in, even in a simple situation like this.

Book1
	A	B
1	Column1	Column2
2	abc	def
3	ghi	jkl
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    CombineColumnLists = List.Combine( {Source[Column1], Source[Column2]} ),
    ConvertedListToTable = Table.FromList(CombineColumnLists, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ChangedType = Table.TransformColumnTypes(ConvertedListToTable,{{"Column1", type text}})
in
    ChangedType

Book1
	D
1	Column1
2	abc
3	ghi
4	def
5	jkl
Sheet1

It took me two tries and maybe 15 minutes to work this out. Using the original data with one row worked, but then broke when I added a row. Went back and got it right!
I manually removed all spaces in steps and renamed the second step from the generic Custom1 to something more relevant.

 

[GraH]

Maybe, Unpivot columns?

 

[jdellasala]

Maybe, Unpivot columns?

Worked fine with one row of data, created two columns with two rows of data, and would create as many columns as there are rows.

 

[GraH]

Like this...

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Cols = Table.ColumnNames(Source),
    Unpivot = Table.Unpivot(Source,Cols,"ColName", "Values"),
    Remove_col_names = Table.RemoveColumns(Unpivot,{"ColName"})
in
    Remove_col_names

 

[jdellasala]

Like this...

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Cols = Table.ColumnNames(Source),
    Unpivot = Table.Unpivot(Source,Cols,"ColName", "Values"),
    Remove_col_names = Table.RemoveColumns(Unpivot,{"ColName"})
in
    Remove_col_names

Very nice! This works even if columns are added. Mine, not so much! Kudos!!

 

[JGordon11]

another way

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Result = Table.FromColumns({List.Combine(Table.ToColumns(Source))})
in
    Result

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7		Column1
2	1	4	7	10	13	16	19		1
3	2	5	8	11	14	17	20		2
4	3	6	9	12	15	18	21		3
5									4
6									5
7									6
8									7
9									8
10									9
11									10
12									11
13									12
14									13
15									14
16									15
17									16
18									17
19									18
20									19
21									20
22									21
23
Sheet4

 

[datatronics505]

another way

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Result = Table.FromColumns({List.Combine(Table.ToColumns(Source))})
in
    Result

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7		Column1
2	1	4	7	10	13	16	19		1
3	2	5	8	11	14	17	20		2
4	3	6	9	12	15	18	21		3
5									4
6									5
7									6
8									7
9									8
10									9
11									10
12									11
13									12
14									13
15									14
16									15
17									16
18									17
19									18
20									19
21									20
22									21
23
Sheet4

Wait, you didn't get any error messages for passing the Source step as the last argument to that function instead of the first?

 

[GraH]

Why do you expect that to return an error?

It works the same as you we're to go split that single line into multiple ones:

T2L = Table.ToColumns(Source),
L = List.Combine(T2L),
L2T = Table.FromColumns(L)

 

[jdellasala]

Wait, you didn't get any error messages for passing the Source step as the last argument to that function instead of the first?

The query references Table5. If you're starting with a new Workbook, that would almost certainly be Table1. I had the same thing happen to me.
The name to the LEFT of the equal sign in M Code is the RESULT of the formula to the right of the equal sign. Source is just the table returned by that function, and by default would be used in the second step nearly always.
Here's that (elegant) code one step at a time:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.ToColumns(Source),
    Custom2 = List.Combine(Custom1),
    ConvertedToTable = Table.FromList(Custom2, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    ConvertedToTable