Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to split a column every two delimiters

Hello, I'm new to Power Bi, and I'm trying to split a column every two commas, but I cannot find the correct argument to do it.

This is how my column looks (this is how the data is being sent to me).

LastName,Name,LastName,Name,LastName,Name

Sometimes is only one name, but it can contain up to 5 names, and I need each one in a separate column.

I hope someone can help me thank you!!!

 

 

Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Well, you could do this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcklNzs5JLdJxL0pN13HKyS/X8cpP1QnOS01N0QlOzFWKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type1",{"Column1.1", "Column1.2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Column1.3", "Column1.4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.1"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Column1.5", "Column1.6"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.2")
in
    #"Merged Columns2"
  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi , Anonymous 

    As metioned by Greg_Deckler，

    If the names are not  many, you can split them all, and then merge   name and Last name  columns one by one.

     

     

     

    You also can refer  to this artice, if there are too many names.

    https://community.powerbi.com/t5/Power-Query/Split-text-based-on-every-second-delimiter/m-p/865873

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.