# Power Query--Split Multiple Columns at one time



## alansidman (May 24, 2020)

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. 

Book1ABCDEF1Column1012342xyz@abc.comCom1|view onlycom2|view only3yog@abc.comCom1|view onlycom2|view onlycom3|view onlySheet2


----------



## sandy666 (May 24, 2020)

```
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 (May 24, 2020)

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 (May 24, 2020)

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 (May 24, 2020)

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 (May 24, 2020)

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 (May 25, 2020)

@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:

Column1Merged.1Merged.2Merged.3Merged.4Merged.5Merged.6xyz@abc.comCom1view onlycom2view onlyyog@abc.comCom1view onlycom2view onlycom3view 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 (May 25, 2020)

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


----------



## alansidman (May 25, 2020)

@*smozgur*

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

Alan


----------



## sandy666 (May 25, 2020)

alansidman said:


> 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


----------



## alansidman (May 24, 2020)

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. 

Book1ABCDEF1Column1012342xyz@abc.comCom1|view onlycom2|view only3yog@abc.comCom1|view onlycom2|view onlycom3|view onlySheet2


----------



## smozgur (May 25, 2020)

alansidman said:


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


----------



## GraH (May 26, 2020)

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 (May 26, 2020)

@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 (May 26, 2020)

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


----------

