Forum Discussion
Best Way to Filter Million Rows of Data
Hello,
One of our users is getting data from an Excel file (stored in network drive) and importing data from SAP HANA database. What he’s doing is filtering the SAP HANA queries based on the table from the excel file which he converted to ‘list’. He is using List.Contains() function to do the filter. The Excel file contains 2000+ rows and it is used to filter multiple SAP HANA queries with million rows.
Any suggestions on what other ways we could try to filter a list of million things down to 2000+?
List.Contains() function seems not efficient and causes slow performance in Power Query.
Thank you!
10 Replies
Jimmy801Community Champion
Hello Anonymous
I've using a lot List.Contains to filter tables. The trick you have to apply ist to surround your list by List.Buffer(YourList). Otherwise it has to load this list for every row, and this will be sloooooooooooooow. I don't know how this would work on millions of rows filtering against 2000 criteria. Try it out and let me know... I would be very curious how this influences the performance
All the best
Jimmy
- edhans
List.Contains() will not be good for this. It is essentially an IN operator, so even on SQL Server, filtering a million rows where a field is IN a list of 2,000 items, It. Will. Be. SLOW.
An inner merge would be faster if you can get the item list on the server so folding happens. List.Contains() will fold even with an Excel list though if it is converted to a list first. A merge of any kind though will not.
Did you convert the Excel numbers to a list prior to using it Anonymous ? If you refer to the list via List.Contains(ExcelFile[Field], [ComparisonField]) that will not fold and be way slower than an inner merge.
edhans what do you mean by "An inner merge would be faster if you can get the item list on the server so folding happens"?
How can I get the item list on the server?
Did you convert the Excel numbers to a list prior to using it @Bernadette ? If you refer to the list via List.Contains(ExcelFile[Field], [ComparisonField]) that will not fold and be way slower than an inner merge. - Yes, it was converted to a list first.
Thank you!
- Greg_Deckler
artemusMicrosoft Employee
Maybe something like this:
= Table.AddColumn(Source, "MatchFound", each Function.ScalarVector(type function(tbl as table [Key=text]) as list, each List.Accumulate([tbl], [results={}, lookuplist = InputList], (current, next) => [results = current[results] & {current[lookuplist]{0}? = next[Key]}, lookuplist = if Value.Compare(next[Key], current[lookuplist]{0}?) > -1 then List.RemoveFirstN(current[lookuplist], 1) else current[lookuplist]])[results])(_))Then you would just filter on MatchFound...
Variables:
Source = The big table
InputList = The filter list
Key = Your key column that you are selecting on.
I haven't used SAP Hana, so I'm not sure if this works well or not?
Edit: Both the list and the table must be sorted.
Actually, this might be faster... depending on the size of your dataset:
Table.FromRecords(List.Transform(InputList, each Source{[Key = _}]), Value.Type(Source))
