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 fromCountryProductPlant % ProducedUSA1Plant A;Plant B 50%;50%USA2Plant B;Plant C 70%;30%USA3Plant A;Plant B 40%;60% To: Country Product Plant % Produced USA1Plant A 50%USA1Plant B 50%USA2Plant B 70%USA2Plant C 30%USA3Plant A 40%USA3Plant B 60% I attach the pbix Thanks!

Forum Discussion

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	Product	Plant	% Produced
USA	1	Plant A;Plant B	50%;50%
USA	2	Plant B;Plant C	70%;30%
USA	3	Plant A;Plant B	40%;60%

To:

Country	Product	Plant	% Produced
USA	1	Plant A	50%
USA	1	Plant B	50%
USA	2	Plant B	70%
USA	2	Plant C	30%
USA	3	Plant A	40%
USA	3	Plant B	60%

I attach the pbix

Thanks!

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.⚡

15 Replies

parry2k

Super User

6 years ago

o59393 start new blank query, click advanced editor, and copy this M code. In fact there are many ways to do but here is one way to do it

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}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Plant", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Plant.1", "Plant.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Plant.1", type text}, {"Plant.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type2", "% Produced", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"% Produced.1", "% Produced.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Plant.2", "% Produced.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Plant.1", "Plant"}, {"% Produced.1", "Produced"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Plant.1", "% Produced.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Plant.2", "Plant"}, {"% Produced.2", "Produced"}}),
    #"Final Table" = Table.Combine({#"Renamed Columns", #"Renamed Columns1"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Final Table",{{"Produced", Percentage.Type}})
in
    #"Changed Type3"

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
Post Prodigy
6 years ago
Hi parry2k

Thanks a lot! Is there another way to do it rather than M code?

For example adding steps to the query?

Let me know if it's possible to have it without having to do so much code 🙂

Thanks!
- parry2k
  Super User
  6 years ago
  o59393 this is steps to the query, not sure if you mean something else? Did you try it?
  
  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
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
  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
    Post Prodigy
    6 years ago
    parry2k amazing
    
    Thanks for solving my problem!
- 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.

Forum Discussion

How to split into rows by semicolons

15 Replies

Related content

Export data with semicolon (;) delimiter

Splitting Column values

split or extract?

Split Value

look up semicolon seperated text from another table without split function