How to recognize landcodes in a column with different structure

[Anonymous]

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.

[Mariusz]

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-intersect

if 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.
LinkedIn

[Anonymous · Accepted solution]

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 find

I 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

[Anonymous]

another 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