Hello again,
I have a connection to a sheet with 5 million rows in the table and want to filter the rows with a value that is in column_1 or in column_2 or in column_3 or in column_4.
So I'm using PQ with a second table containing the search terms and trying to merge the tables.
There might be 100k results from column_1, 50k from column_2, 25k from column_3 and 10k from column_4. I would like a single result with all 185k rows.
I can do this for one column, but struggling to do it on multiple columns. Can you advise please?
Thanks
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!
