Log In
Register
Home
November 9, 2020
Offline
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
Trusted Members
Moderators
January 31, 2022
Offline
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.
November 9, 2020
Offline
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
Trusted Members
Moderators
January 31, 2022
Offline
Thanks, but I don't see a file. Did you press the "Upload" button?
November 9, 2020
Offline
Hmm. Obviously not 🙂
Trusted Members
Moderators
January 31, 2022
Offline
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.
November 9, 2020
Offline
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
Trusted Members
Moderators
January 31, 2022
Offline
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.
1 Guest(s)
