Power Query IF Function - How to search for part of a word and get anything that contains it

[bowlingbear6]

Greetings!

Needing help with a Power Query function.

I am trying to do an if formula thatwill find part of a word and return a true if found. Example if I amtrying to find all words that contain fault I want fault and faulty to be returnedas true. Similar to the search function in excel. I tried using wild card infront of and at the end of the word but that didn’t work.

So after 2 hours ofstruggling I figured I would ask for help.



Thanks inadvance.
Derek

 

[sandy666]

use Conditional Column (menu: Add column - Conditional Column with "contain")

 

[bowlingbear6]

Thank you sandy666 for responding. I was trying that but would only get exact matches. It is excluding words that contain the phrase that don't match exactly.IE....searching for weld would not find welded.

 

[sandy666]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if Text.Contains([Column1], "[B][COLOR="#FF0000"]weld[/COLOR][/B]") then true else null)
in
    #"Added Conditional Column"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]searching for * would not find welded.[/td][td][/td][td=bgcolor:#E2EFDA]searching for * would not find welded.[/td][td=bgcolor:#E2EFDA]

TRUE​

[/td][/tr]
[/table]

you must remember: Power Query is case sensitive