Forum Discussion
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.
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 nullif you are only interested in full matches, you need to use this formula:
if List.Contains(Record.FieldValues(_), "myWord") then 1 else null
10 Replies
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 nullif you are only interested in full matches, you need to use this formula:
if List.Contains(Record.FieldValues(_), "myWord") then 1 else nullThanks for this. Seems to be invalid though.
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 🙂
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.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.
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.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!
