The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am doing a Text.Contains for the word ace. it find ACE HARD, SKYES ACE which is good.... BUT it also finds matches that contain Palace which is bad
whats the best way to test a column contains a specific whole word and not part of a word? thanks
(the text column has multiple words and this is just a short sample)
Solved! Go to Solution.
You could split the text into a list and check if the list contains "ace".
Try this as a custom column:
List.Contains(Text.Split(Text.Lower([Column1]), " "), "ace")
thanks, i tried ace with variations of spaces, but sometimes its first word,ect... also "ace " picks up Palace Ave
ill try Alexis solution and see if its robust acrross many examples.
True, @AlexisOlson, let's make it " ace" OR "ace ". After all, a space after ace finds that case.
Then you'd get true for both "Palace XYZ" and "XYZ Acetone".
In fact, "A space before ace if you do not need space."
"A space before ace gives palace no place."
Just look for " ace".
--Nate
@watkinnc Then you don't get "ACE HARD" but you would get "XYZ ACETONE", right?
You could split the text into a list and check if the list contains "ace".
Try this as a custom column:
List.Contains(Text.Split(Text.Lower([Column1]), " "), "ace")
spaceacespace?
Couldn't resist that, sorry.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
38 | |
21 | |
20 | |
16 | |
13 |