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 not sure what you are trying to do, but you will not see these options thru UI
Post ProdigyI am trying to understand how to build the step by step in power query instead of writing the M code.
In other words is not possible to create the step using the menu bar of power query, only via code?
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Plant.2", "% Produced.2"}),
One problem I have is that if I add a 3rd value for the columns plant and % produced
| Country | Product | Plant | % Produced |
| USA | 1 | Plant A;Plant B:Plant F | 30%;30%;40% |
| USA | 2 | Plant B;Plant C | 70%;30% |
| USA | 3 | Plant A;Plant B | 40%;60% |
The m code wont work. I'd like to have it as flexible as possible, so if a user adds more than 2 or 3 plants the power query should be able to append all the values.
WIll it have to be a static value in the m code?
THanks parry2k