Forum Discussion

o59393's avatar
o59393
Icon for Post Prodigy rankPost 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.⚡

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

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

  • o59393's avatar
    o59393
    Icon for Post Prodigy rankPost Prodigy
    6 years ago

    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.

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago

      o59393 not sure what you are trying to do, but you will not see these options thru UI

      • o59393's avatar
        o59393
        Icon for Post Prodigy rankPost Prodigy
        6 years ago

        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

        CountryProductPlant% Produced
        USA1Plant A;Plant B:Plant F30%;30%;40%
        USA2Plant B;Plant C70%;30%
        USA3Plant A;Plant B40%;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