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

Filter through another Table without merging data?

Hi all,

 

Looking for some help on a question I cannot seem to find a direct answer to online.  I have a Excel 2016 workbook with PowerQuery connections that I am using to draw data from a ticketing system database.  In it, there are many instances where I need to filter one table, based on data from another table, before it is loaded into PowerPivot data model.  Is it possible in power query to filter without merging tables, or is that the only way?

 

Because the dataset is massive (over 1 - 2 mil records), I need to perform as much processing before I load it to PowerPivot, else it weighs down the excel workbook.

 

On a broader scale, this question really has to do with performance, both locally within the workbook as well as with regard to the SQL server.  I am a bit unclear as to the portion of processing that occurs on the SQL server side of things, and the portion that occurs in my local Excel client when I perform queries.  In a few of my workbooks, there are even local worksheets that I Power Query connect to for additional details to be entered as filters not provided by the database.

 

In providing PowerQuery as a solution for reporting needs to my manager, he asked that I identify how front-end/back-end heavy the queries are as they process.

 

To date, I have been merging tables left and right, then filtering as needed, but it got me wondering if I can filter data based off another table without having to merge.  Any info on the other questions would be greatly appreciated as well!

 

Sincerely,

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filter through another Table without merging data?

Hi @KDavidP1987 ,

if you're dragging data from a SQL server, then query folding should take place. That means that the data is filtered at the server side and only the filtered data gets loaded into PQ/PP. There are some limitations that are described here: https://www.mssqltips.com/sqlservertip/3635/query-folding-in-power-query-to-improve-performance/ 

But if you are inner joining against a non-SQL-source to filter the data, there is a catch and you need a workaround that I've described here: https://www.thebiccountant.com/2015/09/17/filter-sql-server-queries-with-excel-tables-query-folding-...

 

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

1 REPLY 1
Super User
Super User

Re: Filter through another Table without merging data?

Hi @KDavidP1987 ,

if you're dragging data from a SQL server, then query folding should take place. That means that the data is filtered at the server side and only the filtered data gets loaded into PQ/PP. There are some limitations that are described here: https://www.mssqltips.com/sqlservertip/3635/query-folding-in-power-query-to-improve-performance/ 

But if you are inner joining against a non-SQL-source to filter the data, there is a catch and you need a workaround that I've described here: https://www.thebiccountant.com/2015/09/17/filter-sql-server-queries-with-excel-tables-query-folding-...

 

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)