Forum Discussion

hello_MTC's avatar
hello_MTC
Helper III
4 years ago
Solved

Split Columns

  Above attached is the column of address and that is the only column i have in my database. I want to split the column by country, state and city. Since the values are not properly merged i ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    hello_MTC , Based on my obervation.

    First Create a new column

    Text.Combine(List.Reverse(Text.Split([Address], ",")), ",")

     

    //I am assuming this will country, state, city, Address order

     

    And then Split by delimiters into column into 4/5 columns as suggested by jaipal 

jaipal's avatar
jaipal
Resolver III
4 years ago

hello_MTC  Open Power Query Editor, Select column and Click on Split Column, choose By Delimiter

 

 

You wil get the columsn as below:

 

And rename the columns

 

  • hello_MTC's avatar
    hello_MTC
    Helper III
    4 years ago

    Thank you for your reply, You can see not all the rows have unique. Some has address,city,state and country and some has only city,state and country.

    I need only City, State and Country.

    • amitchandak's avatar
      amitchandak
      Super User
      4 years ago

      hello_MTC , Based on my obervation.

      First Create a new column

      Text.Combine(List.Reverse(Text.Split([Address], ",")), ",")

       

      //I am assuming this will country, state, city, Address order

       

      And then Split by delimiters into column into 4/5 columns as suggested by jaipal