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 do not understand how to seperate the columns. Please help. Thanks in advance!

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 

3 Replies

  • 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