Forum Discussion
Helper VThe fastest way to return rows that match from another table/list
Hello,
I have a table that has millions of row. This table is the result from other tables after doing its necessary steps, including merging, expanding etc.
I have the following where it's currently returning me only the rows that match from a list.
Table.SelectRows(tblMillionRows, each List.Contains(listItems, [id]) )
The problem here is that, everytime I'm doing a step in the PQ, i.e. AddColumns etc, my top screen where the formula bar is, will have the dots running from left to right, indicating that it's trying to evaluate my formula, and then, at the bottom right, I can see the file size and the file names that I'm pulling from.
My question now is, is there an alternative/faster way for the refresh to work? Seems like no matter what I try to do here, i.e. making them to a List, I kept getting that "spinning" dots of refresh.
If listItems is fixed content (e.g. {"A", "B"}), then adding Table.Buffer makes no difference (at least in actual testing). But if it comes from a Web API, a file, or somewhere else, then you need to add Table.Buffer.
The List.Contains function has the feature of logical short-circuiting, and it will return the result immediately as soon as the result is found. Therefore, you can try to put the frequently appearing values at the front of the list (if the list content is fixed).
When faced with a large amount of data, I usually only provide a small amount of files or data, and then provide all the data after the program testing is completed to improve efficiency.
Hi,
How many items are in the list listItems?
As there are not that much, selecting them using [ID]=X1 or [ID]=X2 or ..... would be more faster than using List.Contains, but if the number are to much, maybe (based on the case) adding a column namely items equal to =listItems and then expand its value and filter the rows whihc [ID]=[items] could be improve the run time,
8 Replies
ZhangKunSuper User
If listItems is fixed content (e.g. {"A", "B"}), then adding Table.Buffer makes no difference (at least in actual testing). But if it comes from a Web API, a file, or somewhere else, then you need to add Table.Buffer.
The List.Contains function has the feature of logical short-circuiting, and it will return the result immediately as soon as the result is found. Therefore, you can try to put the frequently appearing values at the front of the list (if the list content is fixed).
When faced with a large amount of data, I usually only provide a small amount of files or data, and then provide all the data after the program testing is completed to improve efficiency.
- lbendlin
Read about buffers, specifically List.Buffer , and test some variations.
Consider doing this filtering later, in Power BI. Remember that Power Query runs on disk, and Power BI runs in memory, which is orders of magnitude faster.
JustDavidHi Ibendlin,
Tried List.Buffer as well, but it doesn't seems to make any difference (i.e. I have it at List.Contains(List.Buffer(listItems), [id]). I also tried to have another step before, but doesn't make any difference either
buffer = List.Buffer(listItems), selecRow = Table.SelectRows(tblMillionRows, each List.Contains(buffer, [id]) )You mentioned to do this in Power BI. I thought the purpose of Power Query is to clean the data before importing it to Power BI so that lesser/no transformations needed?
- lbendlin
I thought the purpose of Power Query is to clean the data before importing it to Power BI so that lesser/no transformations needed?Indeed it is, but nobody mentioned speed (or lack thereof) at that point.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
- Omid_Motamedise
Hi,
How many items are in the list listItems?
As there are not that much, selecting them using [ID]=X1 or [ID]=X2 or ..... would be more faster than using List.Contains, but if the number are to much, maybe (based on the case) adding a column namely items equal to =listItems and then expand its value and filter the rows whihc [ID]=[items] could be improve the run time, Hi JustDavid,
Thank you for reaching out in Microsoft Community Forum.
Thank you Omid_Motamedise , lbendlin , ZhangKun for the helpful response.
As suggested by Omid_Motamedise, ibendlin, Zhangkun., I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.
Please continue using Microsoft community forum.
Regards,
Pavan.Hi JustDavid,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as "Accept as Solution" and give 'Kudos' if you found it helpful.
Regards,
Pavan.Hi JustDavid,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, kindly "Accept as Solution" and give it a 'Kudos' so others can find it easily.
Thank you,
Pavan.
