Seems about the same, doesn’t it? Except that this time we can’t take advantage of query folding.
Create a query that references the Staging query and….Where Method 1 breaks downīut what if the data set is still too big? What if you need to parameterize the Division, the Date Range and the Department? In order to avoid issues from the formula firewall, you would have to do this: That should take as much advantage as possible, and means that Power Query only needs to run the processing of 500k records against our dynamic criteria. Create a query that references the Staging query and filters the department to the one pulled via the fnGetParameter query.Create the parameter table and the fnGetParameter query.Land this output into a staging query – 500k records total.Filter to only the 2 years of data – Server reduces 10m to 500k records (10/40*2).
Filter to only the Pacific Division – Server reduces 40m to 10m records.The recommended steps would be to do this: (Yes, I’m aware that nothing is that easy… this is an illustration of concept only!) For ease of assumption, we’ll assume that each year is 1/40 of the annual record load and each division provides 1/4 of the total records. Assume that our user (let’s call him Mark) wants to bring in the last 2 years data, is focussing only on the Pacific division, and wants to give the user choice over which department they need to work with. I have no idea what was in the 40 million row data set that the user was working with, but let’s assume it was 40 years of data related to 4 divisions and 30 departments. And from there use your dynamic parameters to filter it down further to get just what you need. Once you have that, land it in a Connection Only query. The goal is to push as much work to the server as possible, resulting in the narrowest and shortest data table that you can accomplish. In order to do that, you should use the user interface to connect to the database and drive as many filter, sort and group by operations as you possibly can. I’m a big fan of query folding, and would encourage you to use it wherever possible.
#EXCEL 2016 QUERY EDITOR MANAGE PARAMETERS HOW TO#
How to Pass Parameters to SQL Queries – Method 1 So are we dead in the water? No, not by a long shot. This almost always involves filtering based on parameters that the user needs to pass at run time. And anyone who knows me is aware that I’m super obsessed with making my data tables as short and narrow as possible. Take a guess as to which one is faster? But what if I want to Pass Parameters to SQL Queries dynamically? In the case of the poster’s question, this means that he just asked to bring 40 million records into Excel, rather than filtering them down on the server. Why is this an issue? Dynamic parameters use custom lines of code, which can’t be folder.
This continues to occur until you hit a command that the database doesn’t know, at which point it returns the data to Power Query, and the rest is done in your instance of Excel. This means that the majority of the heavy lifting is done at the database, not your workstation. While this isn’t technically correct, you can look at query folding like this… As you connect to the database and start filtering and grouping via the user interface, Power Query passes these commands to the database to execute. Power Query is designed to take advantage of query folding when targeted against a database. There are a lot of reasons that you should NOT go this route. Someone asked a question in the forum on the topic, so that finally inspired me to write it up. One of the questions I get quite frequently is how we can pass parameters to SQL queries, allowing us to make them dynamic.