Perhaps you can upload a small data set demonstrating what you have in mind. Raw data as you would find it in the 5 million row table, the table with the search terms and the desired end result.

I've attached a file with an example source table (the original has 5m rows), a table with >1 search terms, and a table what the results would look like.

I can perform 4 merges in PQ to get a results for each column merge and then append the results, but I am wondering if it is possible to perform the search in one merge or similar?

Note that Ethan and Oliver appear in both searches but result in only one 'find'

Thanks

Thanks, but I don't see a file. Did you press the "Upload" button?

Hmm. Obviously not 🙂

The attached file contains two possible solutions. One that begins with a traditional UNPIVOT step, then one merge with the Search table. Filter out the nulls, remove duplicates and merge again with the original source.

An alternative solution seem much neater. It converts the source to one column with lists that are then checked against the items in the Search table. Filter and convert a single column of lists back to a regular table.

Both work, but I can't really oversee which how either of them will perform in i.r.l. on a table with 5 million rows.

Thank you Riny. Very interesting results. I need to get a DAX course!!

I wil have a go with the large file and see how they respond.

Warm regards

Hi Robert,

Glad I could help! And, by the way, DAX relates to Power Pivot / Data Model. You'll need to dive into M, the code language that drives Power Query, in order to follow some of the techniques used here.

Power Query M formula language reference - PowerQuery M | Microsoft Docs

Curious to hear how it goes with real data on a much larger scale.

Of course!