Forum Discussion
Post ProdigyHow to split into rows by semicolons
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.⚡
Super Usero59393 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.⚡
Post ProdigyHi 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
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.⚡
- o59393
Yeah, looks good
Just that I dont understand some steps, so I have 2 questions:
First you removed the columns:
How the removed columns (from previous step) appeared again for the step "remove columns 1" and the other columns were removed (plant.1 and % produced.1) ?
The other questions is the final table step. Is it a button available from here?
I understand it merges all the values, but is it possible to click on any of the button above for me too see how it works?
Thanks!- amitchandak
o59393 , Initial post was split column in rows, the last one you seem to have two columns. I am hopeful parry2k solution has worked.
You can also refer : https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
