Forum Discussion

Badger's avatar
Badger
New Member
4 years ago
Solved

UPDATE Power Query (M) - Text Search & Return Specific # of Characters

UPDATE   Hi All,   I have written this DAX to locate a code within a text field and return the 11 characters which make up the complete code and create a new column from it.   I am trying to ge...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    Hi Badger 

     

    Download this example PBIX file

     

    Try this code, it works for me

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zco7DoAgEIThq0yoJdldfJ7FUCBaGhMkQT29uK2Zbv5vnk1YIvKWdsvi2q4fxolQrvsxvqkxBLAAH4ohn5aIWQTOgbhOJVBf5UIqy5FWq53/zL8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TextCol = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Matches", each let _text = [TextCol] in List.RemoveNulls(List.Transform(WordList , each if Text.PositionOf(_text, _ , 1, Comparer.OrdinalIgnoreCase ) > 0 then Text.Middle(_text, Text.PositionOf(_text , _ , 1, Comparer.OrdinalIgnoreCase), 11) else null))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Matches", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    #"Extracted Values"

     

     

    In your own file you'll just need to add the Custom Column and then extract values from the resultant List(s).

     

    The terms you are searching for are stored in a list called WordList, add to that whatever you need.  Text searching is case insensitive.

     

    Regards

     

    Phil

PhilipTreacy's avatar
PhilipTreacy
Super User
4 years ago

Hi Badger 

 

Download this example PBIX file

 

Try this code, it works for me

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zco7DoAgEIThq0yoJdldfJ7FUCBaGhMkQT29uK2Zbv5vnk1YIvKWdsvi2q4fxolQrvsxvqkxBLAAH4ohn5aIWQTOgbhOJVBf5UIqy5FWq53/zL8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TextCol = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Matches", each let _text = [TextCol] in List.RemoveNulls(List.Transform(WordList , each if Text.PositionOf(_text, _ , 1, Comparer.OrdinalIgnoreCase ) > 0 then Text.Middle(_text, Text.PositionOf(_text , _ , 1, Comparer.OrdinalIgnoreCase), 11) else null))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Matches", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    #"Extracted Values"

 

 

In your own file you'll just need to add the Custom Column and then extract values from the resultant List(s).

 

The terms you are searching for are stored in a list called WordList, add to that whatever you need.  Text searching is case insensitive.

 

Regards

 

Phil

Badger's avatar
Badger
New Member
4 years ago

PhilipTreacy 

 

Thanks for your help.

 

I have noticed that if the search term starts at the beginning of the cell i.e "CATS-45678954 pigeon goat 6535q6" it isn't picked up. if it starts anywhere else "123CATS-123456 pigeon" it is picked up.

 

Any idea why this would be the case?

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    Badger 

     

    My mistake.  The code should check for text from position 0 in the string, onwards.  I had it checking for positions greater than 0.

    Change the code to this

    let _text = [TextCol] in List.RemoveNulls(List.Transform(WordList , each if Text.PositionOf(_text, _ , 1, Comparer.OrdinalIgnoreCase ) >= 0 then Text.Middle(_text, Text.PositionOf(_text , _ , 1, Comparer.OrdinalIgnoreCase), 11) else null))

    Regards

    Phil