Forum Discussion
UPDATE Power Query (M) - Text Search & Return Specific # of Characters
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
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,
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
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?
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