Filter through another Table without merging data?

[Anonymous]

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,

[ImkeF · Accepted solution]

Hi @Anonymous ,

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-limitations-and-bug-workaround/