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

[Badger]

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.

[PhilipTreacy]

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]

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 · Accepted solution]

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]

@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]

@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

[PhilipTreacy]

hi @Badger 

Download sample PBIX file

Create a new column with this

    #"Added Custom" = Table.AddColumn(Source, "Check", each Text.Middle([TextCol], Text.PositionOf([TextCol], "term"), 11))

Regards

Phil

[Badger]

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.