Use wildcard to extract words

[joeywong]

Hi all,

I have a column with unstructured words. I need to extract the words.

AA	AA
AAgotozoo	AA
.AA	AA
AA$$	AA
Zoneedto goAA	AA
$$AA	AA

I tried to add a condition column, but it only extracted certain words but not the complete list. Does anyone know how to use wildcards to extract the text?

Thanks!

[hnguy71]

Hi @joeywong,

Is it a list of text string? Or a very specific wildcard pattern?

[joeywong]

Hi Hnguy71,

It list a of text string. 

Unpredictable nature is a pattern. It can be in front or behind at different times. The words can join together some sometimes. Below are some of the samples.

Thanks!

[hnguy71]

Hi @joeywong ,

To accomplish this, you'll first need a list of keywords to provide such as this:

And then create a custom column to check if the sub-string matches any in the list.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwoyElVitUBsgICfFzBLBUkQRBLwdQUzDE1VUCTSc8vya/KzwcL6CEMALMUVFTAHKB8XmpqSkl+er4CVEksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Words Pattern" = _t]),
    
    // Retrieve list of text strings
    ListPatterns = List.Buffer(KeywordList),
    
    // Find pattern, return first found string, ignoring case-sensitivity.
    ReturnFoundString = Table.AddColumn(Source, "Extract Words", each 
    List.First(
        List.Transform( 
            ListPatterns , (n) => if Text.Contains([Words Pattern], n, Comparer.OrdinalIgnoreCase) then n else null
        )
    )
    ,
    type text)
in
    ReturnFoundString

Expected Output:

I've included a sample pbix for reference.

[joeywong]

Hi hnguy71,

Thank you so much for your input.

I am quite new to M-code. When I tried to apply the code to the real case, there was an expression error.

I import the excel file into Power Bi desktop without putting the code.... "let_t=((type nullable text) meta [seralized.Text=true) in type table....."  Can you elobrate more on this code? Thanks!

[hnguy71]

Hi @joeywong ,

The source originated from when you click "Enter Data". A json document is generated and then converted into a table for additional ETL. Since you already have your own "source", you may either omit or substitue it with your own.

You should ideally start at the ListPatterns step and all the way down. 

If you're having further issues, copy and paste your query here and I can help modify it for you to copy and paste back.

[joeywong]

Hi @hnguy71 ,

Thank you so much for your kind gesture.

I tried to input the suggested M-code but encountered the following errors:

Below is the M-code that I used:

let
Source = Excel.Workbook(Web.Contents("https://smrtcorp-my.sharepoint.com/Documents/Desktop/Dylan%20Files/LBS%20settlement%20(working%20fil..."), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Words Pattern", type text}}),
ListPatterns = List.Buffer(KeywordList),
// Find pattern, return first found string, ignoring case-sensitivity.
ReturnFoundString = Table.AddColumn(Source, "Extract Words", each
List.First(
List.Transform(
ListPatterns , (n) => if Text.Contains([Words Pattern], n, Comparer.OrdinalIgnoreCase) then n else null
)
)
,
type text),
// Find pattern, return first found string, ignoring case-sensitivity.
Table1_Table1 = ReturnFoundString{[Item="Table1",Kind="Table"]}[Extract Words]
in
Table1_Table1

Once again, thank you so much for your help & I really appreciate it.

[hnguy71]

HI @joeywong ,

The error it is stating is that there's not a column called "Word Patterns" which you're attempting to modify or reference. I've changed to the column "Remarks" and this should work for your scenario: 

let

Source = Excel.Workbook(Web.Contents("https://smrtcorp-my.sharepoint.com/Documents/Desktop/Dylan%20Files/LBS%20settlement%20(working%20files)/Joyce%20share%20file/Words%20Patterns.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
ListPatterns = List.Buffer(KeywordList),

// Find pattern, return first found string, ignoring case-sensitivity.
ReturnFoundString = Table.AddColumn(Table1_Table, "Extract Words", each
List.First(
    List.Transform(
        ListPatterns , (n) => if Text.Contains([Remarks], n, Comparer.OrdinalIgnoreCase) then n else null
        )
    ), type text
)

in ReturnFoundString