cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Extract the country from the loaction coloumn

I have a coloumn in my table which tells the location of the user (value), but it includeds city and country sepaerated by delimter and some user contains city,states and Country, since location have two different format,  i am not able to sepaerate country from the coloumn. can anyone help on this

rahulkrishna14_0-1642594699374.png

 




1 ACCEPTED SOLUTION

Accepted Solutions
Syndicate_Admin
Administrator
Administrator

@rahulkrishna14 

In Power Query, click on the column, Under Add Column Tab, click Extract > Text After Delimiter then choose as follow. It should give you the text after the last comma

Fowmy_0-1642595708151.png

 



View solution in original post

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

@rahulkrishna14 

In Power Query, click on the column, Under Add Column Tab, click Extract > Text After Delimiter then choose as follow. It should give you the text after the last comma

Fowmy_0-1642595708151.png

 



Syndicate_Admin
Administrator
Administrator

It looks like you can split by delimiter (,) once (as far right as possible).

What does it look like if you try that?

Syndicate_Admin
Administrator
Administrator

Hi @rahulkrishna14 ,

 

It looks like the country is always the final value in the list. Therefore you can split the list by the right-most delimiter:

 

1) Select the [value] column

2) Go to Transform tab > Split Column (dropdown) > By Delimiter

3) In the dialog that opens, select Comma as the delimiter, then select Right-most delimiter as the 'Split at' choice

4) Crate a new custom column called 'Country' or similar and use this code:

if [value.2] = null then [value.1] else [value.2]

 

This will create a new column accounting for the rows where there is only a country name and no delimiter.

 

You could alternatively use Replace Values instead of the second new column, but that's a bit more complicated to explain.

 

Pete

Syndicate_Admin
Administrator
Administrator

Hi you should create conditional column depend number of coma see below 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8skvVnDMS0/NSS3WUXB21FEIDXZUitWJVnLOyExOTM/XUfD0QQj65Oel5OcB+d5gbnBmXnpiQX5RKpjnmJNaUADUEFxZlJmoFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Count(Text.PositionOf([Column1],",",Occurrence.All ))),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Nb comma"}, {"Column1", "Location"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "City", each if [Nb comma] = 2 then let splitLocation = List.Reverse(Splitter.SplitTextByDelimiter(", ", QuoteStyle.None)([Location])) in splitLocation{2}? else if [Nb comma] = 1 then let splitLocation = List.Reverse(Splitter.SplitTextByDelimiter(", ", QuoteStyle.None)([Location])) in splitLocation{1}? else if [Nb comma]=0 then null else null),
    #"Added Custom3" = Table.AddColumn(#"Added Conditional Column", "Country", each if [Nb comma] = 2 then let splitLocation = List.Reverse(Splitter.SplitTextByDelimiter(", ", QuoteStyle.None)([Location])) in splitLocation{0}? else if [Nb comma] = 1 then let splitLocation = List.Reverse(Splitter.SplitTextByDelimiter(", ", QuoteStyle.None)([Location])) in splitLocation{0}? else if [Nb comma] = 0 then let splitLocation = List.Reverse(Splitter.SplitTextByDelimiter(", ", QuoteStyle.None)([Location])) in splitLocation{0}? else null),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "State", each if [Nb comma] = 2 then let splitLocation = List.Reverse(Splitter.SplitTextByDelimiter(", ", QuoteStyle.None)([Location])) in splitLocation{1}? else if [Nb comma] = 1 then null else if [Nb comma] = 0 then null else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Nb comma"})
in
    #"Removed Columns"

 

The result

freginier_0-1642596552874.png

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Users online (3,049)