Forum Discussion

RobKay12's avatar
RobKay12
Helper I
2 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 co...
Need Help
  • ImkeF's avatar
    ImkeF
    2 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

     

ImkeF's avatar
ImkeF
Community Champion
2 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

 

  • RobKay12's avatar
    RobKay12
    Helper I
    2 years ago

    Thanks for this. Seems to be invalid though.

  • RobKay12's avatar
    RobKay12
    Helper I
    2 years ago

    My appologies. I posted the same question in the DAX section and was thinking i was there when replying to you. I just put the formula in the Query editor and ureka it works.

     

    Thank you very much. I will pay closer attention going forward 🙂