Forum Discussion

o59393's avatar
o59393
Post Prodigy
6 years ago
Solved

How to split into rows by semicolons

Hi all   I have in cells values separated by semicolons ";"   How can I split the rows using Power Query when it detects semicolons in the cells?   In other words, go from Country Produ...
Need Help
Show and Tell
  • parry2k's avatar
    parry2k
    6 years ago

    o59393 here is another way to do this it is fully dynamic, unfortunately you cannot do this kind of transformation using UI.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4vzSspqlTSUQooyk8pTS4BsXIS80C0qgJELDVFKVYnWik02BEoaAhToOBoDaGdgCKmBqrWQIykzgiuzgmqzhkoYg5UZ4yizhireSZAdWYgdbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Country", type text}, {"Product", Int64.Type}, {"Plant", type text}, {"% Produced", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each List.Zip({

Text.SplitAny([Plant],";"), Text.SplitAny([#"% Produced"],";")})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Plant", "% Produced"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Plant", "Produced"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Plant", type text}, {"Produced", Percentage.Type}})
in
    #"Changed Type2"

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi, another way to do in M:

 

#"Added Custom" = Table.AddColumn(#"Changed Type", "CustomPlant", each Table.AddIndexColumn( Table.FromList( Text.Split([Plant],";")),"Index",0,1)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom%", each Table.AddIndexColumn( Table.FromList( Text.Split([#" %  Produced"],";")),"Index",0,1)),
#"Expanded CustomPlant" = Table.ExpandTableColumn(#"Added Custom1", "CustomPlant", {"Column1", "Index"}, {"CustomPlant.Column1", "CustomPlant.Index"}),
#"Expanded Custom%" = Table.ExpandTableColumn(#"Expanded CustomPlant", "Custom%", {"Column1", "Index"}, {"Custom%.Column1", "Custom%.Index"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Custom%", "Custom", each [CustomPlant.Index]=[#"Custom%.Index"]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Custom] = true)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"CustomPlant.Index", "Custom%.Index", "Custom"})
in
#"Removed Columns"

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    o59393 here is another way to do this it is fully dynamic, unfortunately you cannot do this kind of transformation using UI.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs4vzSspqlTSUQooyk8pTS4BsXIS80C0qgJELDVFKVYnWik02BEoaAhToOBoDaGdgCKmBqrWQIykzgiuzgmqzhkoYg5UZ4yizhireSZAdWYgdbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Country", type text}, {"Product", Int64.Type}, {"Plant", type text}, {"% Produced", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each List.Zip({

Text.SplitAny([Plant],";"), Text.SplitAny([#"% Produced"],";")})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Plant", "% Produced"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Plant", "Produced"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Plant", type text}, {"Produced", Percentage.Type}})
in
    #"Changed Type2"

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

    • o59393's avatar
      o59393
      Post Prodigy
      6 years ago

      parry2k  amazing

       

      Thanks for solving my problem!

  • o59393's avatar
    o59393
    Post Prodigy
    4 years ago

    Hi Anonymous 

     

    This solution you provided is amazing too. It worked great for other project I am doing.

     

    Thank you.