Forum Discussion
Clean column from delimiter
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
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.
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
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.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