Clean column from delimiter

[Anonymous]

I have a column with City, number, Country. 

Some of the rows only have number, Country and some have Country.

I would like to clean it so it is only showing the Countries. 

See picture: 

[watkinnc]

I would add a column:

Table.AddColumn(LastStep, "Cleaned", each if Text.Contains([SYSTEM_LOCATION], ",") then Text.AfterDelimiter([SYSTEM_LOCATION, ",", {1, RelativePosition.FromStart}) else [SYSTEM_LOCATION])

[AlB]

Hi @Anonymous 

If the country is always at the end, you can just split the text based on the commas and take the last item of the resulting list. Paste this M code on a blank query to see the steps on an example (the custom column is the relevant one):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5PTUnNU4rViVYKzcssSU1RCC5JLEktBov4F+fk6xga6Sj45ReVJ1aCxYyMdRTcihLzklOVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SYSTEM__LOCATION = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SYSTEM__LOCATION", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Last(Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)([SYSTEM__LOCATION])))
in
    #"Added Custom"

 Take into account however that this will fail in cases where the contry name includes a comma, like "Korea, Republic of". You'll have to come up with a method to tell those cases apart.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB 
I can't see that this is working as it should.

As you can see in the picture the country in custom.custom is not the same as the source country in SYSTEM.LOCATION. 

The only countries that are showing in the custom column is Sweden, United States, Norway and France - and those are not even linked correct as it seems. When I choose sweden I got all of the countries. 

[AlB]

Hi @Anonymous 

It seems to be working based on the column Custom.SYSTEM_LOCATION, doesn't it? Your code is probably processing the incorrect column (Custom.SYSTEM_LOCATION instead of SYSTEM_LOCATION); that should be easy to change. If you share a data sample (or the full M code of your query) we can work on it. You see that it works in the example i provided, right?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB 
Yes, it is working with that column. But I want it ti refer to the column SYSTEM_LOCATION.
I took the code you shared with me and it made it like that. 

[AlB]

@Anonymous 

Like I said, the code is probably referring to the wrong column and should be easy to fix, but I need to see your M code to be able to see where the issue is. Or if you can share your pbix file, even better.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@ImkeF , can you help on this