Power Query--Split Multiple Columns at one time

[alansidman]

In attached is a sample. I wish to split the columns that have been merged with the pipe delimiter. I now wish to split each of these columns. I can do this one column at a time, but I wish to do this with one line of Mcode.

Book1
	A	B	C	D	E	F
1	Column1	0	1	2	3	4
2	xyz@abc.com		Com1|view only		com2|view only
3	yog@abc.com	Com1|view only		com2|view only		com3|view only
Sheet2

 

[smozgur]

Code is very clean. Like the fact that no nulls in between. Thanks for the heads up on XL2BB

 

[GraH]

Hi @alansidman,

you got me fooled with "I wish to do this with 1 line of M-code".

I got very quickly to the code below which seems to handle the null columns without any hassle.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(Record.ToList(Source{_}),"|"))
in
    #"Added Custom"

but then struggled to have the export done in a way that's future proof. Need to look further into the code smozgur made as he managed to do in a clean way as you said.

Nice work by @sandy666 and @billszysz too.

 

[alansidman]

@GraH

you got me fooled with "I wish to do this with 1 line of M-code".

I guess you figured out what I meant by that. Not having to do each column individually. This has been a great learning experience. All four of you have provided me with some new insight on this particular item. Thanks again.

 

[sandy666]

my two cents with another approach

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOC = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
    Sort = Table.Sort(UOC,{{"Attribute", Order.Ascending}}),
    Split2Rows = Table.ExpandListColumn(Table.TransformColumns(Sort, {{"Value", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    Index0 = Table.AddIndexColumn(Split2Rows, "Index", 0, 1),
    RC = Table.RemoveColumns(Index0,{"Attribute"}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(RC, {{"Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(RC, {{"Index", type text}}, "en-GB")[Index]), "Index", "Value")
in
    Pivot

this is not "one-line" code of course