Best Way to Filter Million Rows of Data

[Anonymous]

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!

[Jimmy801]

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

[justinh]

I do what @Jimmy801 does, I wrap the list in a List.Buffer first. 

NewStep = Table.SelectRows(#"Previous Step", let _list = List.Buffer(ExcelList) in each List.Contains(_list, [HanaColumn]))

[edhans]

Does that fold with a SAP Hana db @justinh ? I don't have enough experience with that, nor the means to test.

[justinh]

@edhans 
Yes it folds with Hana. It can result in a stack overflow if the list is too large. 2000 items may be cutting it close.

[edhans]

Thanks @justinh - useful info. I've done it on SQL Server with more than that with no issues. Each database and how Power Query operates with it is different.

Merging is still faster, but you must be 100% on the server for that to fold.

[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.

[Anonymous]

@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!

[edhans]

You would need to work with your IT department/Database admins to get the list natively on your server. There are ways using Power Automate, stored procedures, scripts, etc. to get that data on a SQL server so it is now native to the database and will allow the server to do all of the work via folding. But that will be up to your DBA and the possibilities with the SAP Hana database.

If you are in a SQL environment, List.Contains() will fold if done properly. I have an article about this here. I cannot say whether or not that will fold in a SAP Hana environment. If you can see the native query after the step with List.Contains() you will know that is folding. It is possible in SAP Hana that isn't folding either. 

[artemus]

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))

[Greg_Deckler]

@Anonymous - Don't use List.Contains but rather a merge query? Table.Join. But I will leave it to the experts in this such as @ImkeF and @edhans I think that biaccountant.com or maybe Chris Webb's blog had some articles on speeding up joins and such.