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

 

[sandy666]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Merge = Table.CombineColumns(Source,{"Column1", "0", "1", "2", "3", "4"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    Split = Table.SplitColumn(Merge, "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8", "Merged.9"})
in
    Split

 

[sandy666]

or even like this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TCC = Table.CombineColumns(Source,{"0", "1", "2", "3", "4"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    Split = Table.SplitColumn(TCC, "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8"}),
    Demote = Table.DemoteHeaders(Split),
    TransposeA = Table.Transpose(Demote),
    Replace = Table.ReplaceValue(TransposeA,"Merged.","",Replacer.ReplaceText,{"Column1"}),
    TransposeB = Table.Transpose(Replace),
    Promote = Table.PromoteHeaders(TransposeB, [PromoteAllScalars=true])
in
    Promote

but M is much longer

 

[sandy666]

or like this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TCC = Table.CombineColumns(Source,{"0", "1", "2", "3", "4"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    Split = Table.SplitColumn(TCC, "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8"}),
    Demote = Table.DemoteHeaders(Split),
    TransposeA = Table.Transpose(Demote),
    Index = Table.AddIndexColumn(TransposeA, "Index", -1, 1),
    TypeText = Table.TransformColumnTypes(Index,{{"Index", type text}}),
    Replace = Table.ReplaceValue(TypeText,"-1","Column1",Replacer.ReplaceText,{"Index"}),
    TRC = Table.RemoveColumns(Replace,{"Column1"}),
    TSC = Table.SelectColumns(TRC,{"Index", "Column2", "Column3"}),
    TransposeB = Table.Transpose(TSC),
    Promote = Table.PromoteHeaders(TransposeB, [PromoteAllScalars=true])
in
    Promote

as you know there are many ways to do that

 

[billszysz]

one more solution

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
  Result = List.Accumulate(
    Table.ColumnNames(Source), 
    Source, 
    (st, cur) => 
      if List.AnyTrue(List.Transform(Table.Column(st, cur), each Text.Contains(_, "|")))
      then Table.SplitColumn(st, cur, Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 2)
      else st
  )
in
  Result

 

[sandy666]

thanks Zbyszek
you show me that: Nothing should be done in haste but gripping a flea

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ReplacedNull = Table.ReplaceValue(Source,null,"|",Replacer.ReplaceValue,{"Column1", "0", "1", "2", "3", "4"}),
    TCC = Table.CombineColumns(ReplacedNull,{"0", "1", "2", "3", "4"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    SplitCount = Table.AddColumn(TCC, "SCount", each List.Count(Text.Split([Merged],"|"))),
    MaxCount = List.Max(SplitCount[SCount]),
    Split = Table.SplitColumn(TCC, "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), MaxCount),
    Demote = Table.DemoteHeaders(Split),
    TransposeA = Table.Transpose(Demote),
    Index = Table.AddIndexColumn(TransposeA, "Index", -1, 1),
    TypeText = Table.TransformColumnTypes(Index,{{"Index", type text}}),
    Replace = Table.ReplaceValue(TypeText,"-1","Column1",Replacer.ReplaceText,{"Index"}),
    TRC = Table.RemoveColumns(Replace,{"Column1"}),
    TSC = Table.SelectColumns(TRC,{"Index", "Column2", "Column3"}),
    TransposeB = Table.Transpose(TSC),
    Promote = Table.PromoteHeaders(TransposeB, [PromoteAllScalars=true])
in
    Promote

 

[smozgur]

@alansidman: I am probably wrong but for some reason (and I am recently very interested in Power Query), I feel like that you don't need blank cells in columns in between.
I mean; result like this:

Column1	Merged.1	Merged.2	Merged.3	Merged.4	Merged.5	Merged.6
xyz@abc.com	Com1	view only	com2	view only
yog@abc.com	Com1	view only	com2	view only	com3	view only

So here is my M-Code attempt with this assumption:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Merge = Table.AddColumn(Source, "Merged", each Text.Combine(List.Transform(List.Skip(Record.FieldValues(_), 1),Text.From),"|")),
    CleanUp = Table.SelectColumns(Merge,{Table.ColumnNames(Merge){0}, "Merged"}),
    Destination = Table.SplitColumn(CleanUp, "Merged", 
        Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 
        List.Max(List.Transform(CleanUp[Merged], each List.Count(Text.PositionOf(_,"|",Occurrence.All )))) + 1)
in
    Destination

Note: Alan - I see that you are using the first release of XL2BB. Just FYI - there is a newer version in case you'd like to update.

 

[alansidman]

Big Thanks Sandy. I was pulling my hair trying to figure this one out.

 

[alansidman]

@smozgur

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

Alan

 

[sandy666]

Big Thanks Sandy. I was pulling my hair trying to figure this one out.

Hi Alan,
Basic code (post#6) is to the Split step. The rest is a game (bells&whistles ) with headers only