cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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 10
Super User IV
Super User IV

@Bernadette - 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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Microsoft
Microsoft

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

 

Super User II
Super User II

Hello @Bernadette 

 

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

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

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. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors