Forum Discussion

RobKay12's avatar
RobKay12
Icon for Helper I rankHelper I
3 years ago
Solved

Looking for a specific formula

I am drawing a blank and I know if it is possible, that it is probably a easy answer. I have a table with over 1000 rows and each row has hundreds of columns. 

I want to add a forumla in the last column to say that if a specific word is found in that row then make that column a 1, otherwise leave it blank. 

I know how to do this if I point to each and every column but was wondering if there was a shorter formula.

Thank you for any assistance.

Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    Hi RobKay12 ,
    if you add column with this formula, you will get a match for each substring:

    if Text.Contains(Text.Combine(List.Transform(Record.FieldValues(_), (l)=> Text.From(l)),  " "), "myWord") then 1 else null

    if you are only interested in full matches, you need to use this formula:

    if List.Contains(Record.FieldValues(_), "myWord") then 1 else null