Append contents of one column to another in Power Query

datatronics505

Board Regular
Joined
Nov 26, 2022
Messages
55
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hi,
I am stuck at appending contents of one column to another in Power Query. In the simplest of examples:
Column1Column2
abcdef

should turn into:

Column1
abc
def
Again, in Power Query.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
AB
1Column1Column2
2abcdef
3ghijkl
Sheet1

Power Query:
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
1Column1
2abc
3ghi
4def
5jkl
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.
 
Upvote 0
Like this...
Power Query:
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
 
Upvote 0
Like this...
Power Query:
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!!
 
Upvote 0
another way

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

Book1.xlsx
ABCDEFGHIJ
1Column1Column2Column3Column4Column5Column6Column7Column1
2147101316191
3258111417202
4369121518213
54
65
76
87
98
109
1110
1211
1312
1413
1514
1615
1716
1817
1918
2019
2120
2221
23
Sheet4
 
Upvote 0
another way

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

Book1.xlsx
ABCDEFGHIJ
1Column1Column2Column3Column4Column5Column6Column7Column1
2147101316191
3258111417202
4369121518213
54
65
76
87
98
109
1110
1211
1312
1413
1514
1615
1716
1817
1918
2019
2120
2221
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?
 
Upvote 0
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:
Power Query:
T2L = Table.ToColumns(Source),
L = List.Combine(T2L),
L2T = Table.FromColumns(L)
 
Upvote 0
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:
Power Query:
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
 
Upvote 0

Forum statistics

Threads
1,223,401
Messages
6,171,893
Members
452,431
Latest member
TiffanyMcllwain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top