Clean column from delimiter

Question

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:

amitchandak · Answer

ImkeF , can you help on this

alb · Answer

Hi Anonymous&nbsp;
If the country is always at the end, you can just split the text&nbsp;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&nbsp;custom column is the relevant one):
&nbsp;
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"
&nbsp;
&nbsp;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&nbsp;
&nbsp;

anonymous · Answer

AlB&nbsp;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.&nbsp;&nbsp;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.&nbsp;

alb · Answer

Hi Anonymous&nbsp;
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?
&nbsp;
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&nbsp;
&nbsp;

anonymous · Answer

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