Forum Discussion
How to recognize landcodes in a column with different structure
Dear pbi users,
I am using data from google Adwords and want to make the costs visible per landcode. Most data is structurered as i marked in the screen below. This works ok because i can just use a delimiter "-".
But there is also data where the landcode is after the 2nd or 3rd "-".
My target is to get the landcodes in 1 column, independently if the landcode is after the 1ste, 2nd or 3rd delimiter. Is this possible?
Thanks in advance.
you should somehow classify the various cases then use these criteria within nested blocks if .. then .. else and extract extract the parts of interest with functions like:
Text.AfterDelimiter ([Column1], "-", {0, RelativePosition.FromEnd}))
which extracts the final part after the last "-"
and similar functions you findI m ean somethink like the following
Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([Column1],"DA") then Text.BetweenDelimiters([Column1], "-","-") else Text.AfterDelimiter ([Column1], "-",{0,RelativePosition.FromEnd}))
https://docs.microsoft.com/it-it/powerquery-m/text-betweendelimiters
3 Replies
Hi Anonymous
You can use Text.Split to split text into list
https://docs.microsoft.com/en-us/powerquery-m/text-split
Create an extra table/ list of all the landcodes and use List.Intersect on text splited by delimiter and the list of all the codes, it will result in a list of landcodes if any matches.
https://docs.microsoft.com/en-us/powerquery-m/list-intersectif you would like to see a working solution, please paste a sample into a body of a post.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedInyou should somehow classify the various cases then use these criteria within nested blocks if .. then .. else and extract extract the parts of interest with functions like:
Text.AfterDelimiter ([Column1], "-", {0, RelativePosition.FromEnd}))
which extracts the final part after the last "-"
and similar functions you findI m ean somethink like the following
Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([Column1],"DA") then Text.BetweenDelimiters([Column1], "-","-") else Text.AfterDelimiter ([Column1], "-",{0,RelativePosition.FromEnd}))
https://docs.microsoft.com/it-it/powerquery-m/text-betweendelimitersanother way could be following this scheme
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnHUdXFV0FVITy3KTcyrVIrVAYu5BQHF0ooS85JToUJAvnNUjXMUguvnXuPqB+ZmZGVkAwWystPTs7OADBdXsHBKegqQk5GWDiShKvNyc4EcN5dgkFCwUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([Column1],"DA") then Text.BetweenDelimiters([Column1], "-","-") else Text.AfterDelimiter ([Column1], "-",{0,RelativePosition.FromEnd})), #"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom.1", each List.Select(Text.SplitAny([Column1]," -|"), each Text.Length(_)=2 and not Text.Contains(_,"DA"))), #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}) in #"Extracted Values"or if the criteria that the land code must be length 2 chars in not always true, but it is true that every land code is all capital letters and all the other words, except "DA", are mixed upper and lower case:
#"Added Custom2" = Table.AddColumn(#"Extracted Values", "Custom.2", each List.Select(Text.SplitAny([Column1]," -|"), each List.ContainsAll({"A".."Z"},Text.ToList(_)) and not Text.Contains(_,"DA"))), #"Extracted Values1" = Table.TransformColumns(#"Added Custom2", {"Custom.2", each Text.Combine(List.Select(_, each _ <>""), "|"), type text})or ...
the other cases you know
