Forum Discussion

o59393's avatar
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 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
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.⚡

o59393's avatar
o59393
Post Prodigy
6 years ago

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!

    • parry2k's avatar
      parry2k
      Super 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
        Post 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.