Merge Multiple Columns in One code

[Shreesurya]

Hi,

I am Merging 2 columns in Power query and its being repeated for rest of the table.

Col A & B are merged and then C&D and then E &F, finally i will have 3 Columns from 6 Columns.

Problem here is when I merge every time new line of code being created. is there a possibility in Power query to use Nested Merge and have just one line of code instead of having each line for every merge I do.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Column1", type text}, {"Column2", type text}}, "en-US"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"A"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Column3", type text}, {"Column4", type text}}, "en-US"),{"Column3", "Column4"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"B"),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Column5", type text}, {"Column6", type text}}, "en-US"),{"Column5", "Column6"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"C"),
    #"Merged Columns3" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns2", {{"Column7", type text}, {"Column8", type text}}, "en-US"),{"Column7", "Column8"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"D"),
    #"Merged Columns4" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns3", {{"Column9", type text}, {"Column10", type text}}, "en-US"),{"Column9", "Column10"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"E"),
    #"Merged Columns5" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns4", {{"Column11", type text}, {"Column12", type text}}, "en-US"),{"Column11", "Column12"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"F")
in
    #"Merged Columns5"

 

[JGordon11]

Here is a query that takes a table with an assumed even number of columns of text and joins with a comma cols 0 & 1 , 2 & 3, ... n-2 & n -1 where n is the total number of columns. The resulting table column names are A,B,C... and the resulting table has n/2 columns. If the column data is not text it won't work and would need to be modified.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst = Table.ToColumns(Source),
    eolist = Table.ColumnCount(Source)/2-1,
    lst1 = List.Accumulate({0..eolist}, {}, (s,c)=> s & {List.Zip({lst{c*2}, lst{c*2+1}})}),
    lst2 = List.Transform(lst1, each List.Transform(_, (x)=> Text.Combine(x,","))),
    Headers = List.Transform({0..eolist}, each Character.FromNumber(65+_)),
    Result = Table.FromColumns(lst2, Headers)
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10		A	B	C	D	E
2	A	E	I	M	Q	U	AA	EE	II	MM		A,E	I,M	Q,U	AA,EE	II,MM
3	B	F	J	N	R	V	BB	FF	JJ	NN		B,F	J,N	R,V	BB,FF	JJ,NN
4	C	G	K	O	S	W	CC	GG	KK	OO		C,G	K,O	S,W	CC,GG	KK,OO
5	D	H	L	P	T	X	DD	HH	LL	PP		D,H	L,P	T,X	DD,HH	LL,PP
6
Sheet1

 

[Shreesurya]

Thank you so much for the code Gordon, Is it possible to enhance this code where I can specify which columns to merge (By Providing column Headers), Because my data doesn't come with the same column numbers, however, the Header name would be the same. For Example, data in Column C might be there in Column D.

 

[JGordon11]

Maybe like this?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ColsToMerge = {{"Col5","Col6","Merge5&6"},{"Col7","Col8","Merge7&8"}},
    ColsZip = List.Transform(ColsToMerge, each List.Zip({Table.Column(Source, _{0}),Table.Column(Source, _{1})})),
    ColsMergedNames = List.Transform(ColsToMerge, each _{2}),
    ColsCombine =List.Transform(ColsZip, each List.Transform(_, (x)=> Text.Combine(x,", "))),
    xCols = List.Accumulate(ColsToMerge, {}, (s,c)=> s & List.FirstN(c,2)),
    xTable = Table.SelectColumns(Source, List.RemoveItems(Table.ColumnNames(Source),xCols)),
    Result = Table.FromColumns(Table.ToColumns(xTable) & ColsCombine, Table.ColumnNames(xTable) & ColsMergedNames)
in
    Result

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10		Col1	Col2	Col3	Col4	Col9	Col10	Merge5&6	Merge7&8
2	A	E	I	M	Q	U	AA	EE	II	MM		A	E	I	M	II	MM	Q, U	AA, EE
3	B	F	J	N	R	V	BB	FF	JJ	NN		B	F	J	N	JJ	NN	R, V	BB, FF
4	C	G	K	O	S	W	CC	GG	KK	OO		C	G	K	O	KK	OO	S, W	CC, GG
5	D	H	L	P	T	X	DD	HH	LL	PP		D	H	L	P	LL	PP	T, X	DD, HH
6
Sheet1

 

[Shreesurya]

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], ColsToMerge = {{"Col5","Col6","Merge5&6"},{"Col7","Col8","Merge7&8"}}, ColsZip = List.Transform(ColsToMerge, each List.Zip({Table.Column(Source, _{0}),Table.Column(Source, _{1})})), ColsMergedNames = List.Transform(ColsToMerge, each _{2}), ColsCombine =List.Transform(ColsZip, each List.Transform(_, (x)=> Text.Combine(x,", "))), xCols = List.Accumulate(ColsToMerge, {}, (s,c)=> s & List.FirstN(c,2)), xTable = Table.SelectColumns(Source, List.RemoveItems(Table.ColumnNames(Source),xCols)), Result = Table.FromColumns(Table.ToColumns(xTable) & ColsCombine, Table.ColumnNames(xTable) & ColsMergedNames) in Result

Hi Gordon,

This is awesome. this coding worked the way I wanted, Thank you so much for helping...