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 get the same result in M instead of using DAX but can't seem to figure it out.

 

check =
if (containsString('Table'[Column],"term"),
(MID(('Table'[Column]), (FIND("term", 'Table'[Column], 1, Blank())), 11)
Blank()
)

 

Does anyone know if this is possible in M and could you please point me in the right direction?

 

How would I enter it in the 'add custom column' and search for multiple terms?
The code starts with Term1 or Term2 or Term3 ect.

 

Cheers.

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

7 Replies

    • Badger's avatar
      Badger
      New Member
      4 years ago
      Hi Phil,

      Thanks for your help I'm pretty new to Power BI and your assistance is appreciated.

      I have a follow on question:

      How would I enter that in the 'add custom column' and search for multiple terms?
      The code starts with Term1 or Term2 or Term3 ect.

      Thanks for everything.
  • 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