Add column based on wildcard search from another column

[jps]

I have a column lets say “Column A” that includes string in mixed cases and special characters etc.

I want to create a new column “Column B” that basically copies all the String / text from Column A only if the string contains word “Level”, and L can be in any case.

Is there a way to do this with M code, in Power Query ?

I guess I am looking at something similar to SQL Like % text% type of query

[Melissa]

Hi @jps,

Please provide a sample for “Column A” values, with expected results.
Thanks!

You can give this a go but at this time I can’t be sure it matches your requirement… just paste the full script below into a New Blank Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYw7DsIwEAWv8uQarkEVJJDSWS6ssCQr22y0thG5PWHTUM68j/duoDdl9BWbdMXtjpo45+rCybsLa23Iv4bxIFPi12xGzYzyF48LoS2sj8NBnjsSSs+Nz7WJ0oYpKtaoyQZW2w8NrvHDpZfjPM7kQvgC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    AddCustom = 
        Table.AddColumn( Source, "Column2", each 
            if List.AnyTrue(
                List.Transform(
                    Text.Split([Column1], " "), each 
                    Text.Contains(_ & " ", "level ", Comparer.OrdinalIgnoreCase)
                )
            )
            then [Column1]
            else null
        )
in
    AddCustom

[jps]

Hey Melissa,
Here is a sample data

This is what I eventually want to achieve:

So the first and last row have the word “Level” and this can appear in any case

I just wonder if it will be easier to achieve this in DAX after I load the data

[Melissa]

Hi @jps,

Just updated the splitter function and the ‘then’ clause

.
Here’s the full solution, just paste this into a new blank query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6xDsMgDATQX0HMnfILWbt1RAg5yG1pILbApOHvazXq0BtPT6dzzr4AmKq54o45hGMyT8jpDoeZ5xAiD6MpWGVNe2khJEVUrL8423ruvJlbZx2Q00sdmJWUylSYBoCWJ+cF8j+OeQjKWzXFBII6H+lr11S2h2mMFarQ79wyWe8/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    AddCustom = 
        Table.AddColumn( Source, "Column2", each 
            if List.AnyTrue(
                List.Transform(
                    let mySplit = Splitter.SplitTextByAnyDelimiter( {" ", "__"} ) in mySplit( [Column1] ), each 
                    Text.Contains( _ & " ", "level ", Comparer.OrdinalIgnoreCase)
                )
            )
            then "Escalation"
            else null
        , type text )
in
    AddCustom

I hope this is helpful.

[EnterpriseDNA]

Hi @jps, good to see that you are having progress with your inquiry.

Did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

[jps]

Thank you for the solution

I am sure there is something I am not doing correct. as I am little unfamiliar with M code. Not sure where to make changes to match the dataset in my Power BI.

So the source Table is named AG,
The Column1 is named Engineers,

Now I am looking to add a column in this table, it already has 10 columns … Should I just be pasting the above code in a new query ? I guess ill need to replace some column name or table names in above code. not sure where exactly.

[Melissa]

Hi @jps,

1. Replace with your Table called AG
2. Replace with your Column called Engineers
   
   

OR add the highlighted part to your AG query

1. Replace with the Previous Step name (in the AG query)
2. Replace with your Column called Engineers
   
   

I hope this is helpful

[jps]

Brilliant! That worked Smooth !!

Thank you So Much - Really Appreciate your support