Looking for a specific formula

[RobKay12]

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.

[ImkeF]

No worries 🙂
Just make sure to close your question over in the DAX-forum.
And please mark my solution as a solution, so this thread can be closed as well.
Thx!

[ImkeF]

Also, there is no concept in DAX for such a dynamic approach.
You would have to do it in Power Query using the M-language.

[ImkeF]

Hi @RobKay12 ,
this is the forum section for Power Query (in the query editor).
You cannot use DAX Studio for it.

[ImkeF]

Has been valid for me.
If you would post the error-message and your M-code we could make it work for you as well.

[RobKay12]

[ImkeF · Accepted solution]

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]

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 🙂

[RobKay12]

Thanks for this. Seems to be invalid though.

[ImkeF]

Hello @RobKay12 ,
can that word be found as a substring anywhere in any cell/column or must it match the whole columns value?

[RobKay12]

It would be a specific word. As long as it was found atleast once in any column of the row then I would like it to return a 1.