Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Cheers.
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
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
Proud to be a Super User!
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
Proud to be a Super User!
hi @Badger
Create a new column with this
#"Added Custom" = Table.AddColumn(Source, "Check", each Text.Middle([TextCol], Text.PositionOf([TextCol], "term"), 11))
Regards
Phil
Proud to be a Super User!