Forum Discussion
How 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.⚡
o59393 it is pretty straight forward, and I think you are already got the gist but here it is again and all this code was manually typed
- split column step #"Split Column by Delimiter1" is our table which we want to work on
- first, we removed produced 2% and Plant 2 %
- we left with produced 1% and plant 1% and then renamed to products and plant
- in removed step 1, we again referred back to split column step and this time we remove produced 1 and plant 1
- renamed it to plan and produced
- so we have two tables in rename steps
- and these were combined (appended ) in the final step
and that's 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.⚡
Thanks parry2k
Looking at the code the "remove columns 1" has within parenthesis "split columns by delimter"
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Plant.2", "% Produced.2"}),
If I did a remove column step from the icon
It would look like this the code:
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Plant.2", "% Produced.2"}),
How can I do the
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Plant.2", "% Produced.2"}),
Using the options in the home/transfrom/add panel?
Im still lost how you were able to refer back the deleted values/columns and have 2 tables. I only see one for the append step:
Thanks and apologyze for not getting it that quick.
I 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