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 

 

Are you actually searching for strings that begin "Term..." or are you using the word Term as a placeholder?

 

If you can provide some example data it will make it clearer.  Be as specific as possible.  When you say you are searching for Term1 or Term2 or Term3 ect. - what is covered by etc?  Writing code to cover all possibilities may or may not be complicated, it depends on the details of what you are looking for.

 

Regards

 

Phil

  • Badger's avatar
    Badger
    New Member
    4 years ago

    Hi PhilipTreacy ,

     

    Yes, "Term "is a placeholder. I should have been clearer, I apologise.

     

    I can't really share the data due to its nature,  but I can elaborate on the terms.

     

    I need to find similar to the following and the following 6 numbers:

    "Word-"

    "Term-"

    "Cats-"

    There are 8 of these terms in total and they all have the same format of 4 letters followed by a - then 6 numbers.

     

    The column we are searching is a comments field from a database that has been populated by lazy people 😛

     

    Many Thanks,

    • 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?