Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jhalverson
Helper I
Helper I

Does Filtering Rows in Power Query Editor Boost Performance Significantly?

Hi all.

 

I know this is kind of an ambiguous question, but I am curious whether filtering some of my tables is worth it.  Say I had a table with 300,000 rows and I filter out 200,000 of them? Since 2/3 of the rows are eliminated, should Power BI theoretically take 1/3 the time to load the table in? 

 

Also, is filtering an expensive operation in the Power Query editor? Should it be done at a certain point when editing a table in Power Query editor? Feel free to post any links if they are useful to this topic.

 

Appreciate the help!

1 ACCEPTED SOLUTION
Tutu_in_YYC
Resident Rockstar
Resident Rockstar

My opinion is yes, it is worth filtering the tables. 

But removing 2/3 of rows doesnt directly equal to reducing the load time to 1/3. Because the load time is not just for loading the rows, it is also for evaluating queries, converting to sql (if folding is possible) and more.

Best practice is always try to make sure that the query can be folded to SQL , so the the query can be evaluated in the source system. BUt of course only works with SQL databases.

 

When you have less data/rows, you DAX calculations will be faster too.

View solution in original post

2 REPLIES 2
ribisht17
Super User
Super User

Hi jhalverson,

 

As mentioned above it can improve the performance but in order to improve the performance, there are multiple factors to be taken care of /or say you should be aware of

 

I hope this link will help you to some extent Power BI - Report performance best practices (datapears.com)

 

Thanks,

Ritesh

Tutu_in_YYC
Resident Rockstar
Resident Rockstar

My opinion is yes, it is worth filtering the tables. 

But removing 2/3 of rows doesnt directly equal to reducing the load time to 1/3. Because the load time is not just for loading the rows, it is also for evaluating queries, converting to sql (if folding is possible) and more.

Best practice is always try to make sure that the query can be folded to SQL , so the the query can be evaluated in the source system. BUt of course only works with SQL databases.

 

When you have less data/rows, you DAX calculations will be faster too.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.