cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
ImkeF
MVP

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

 

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

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
ImkeF
MVP

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

 

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

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 Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,915)