One of the questions I get quite frequently is how we can pass parameters to SQL queries, allowing us to make them dynamic. Someone asked a question in the forum on the topic, so that finally inspired me to write it up.
Before you do this…
There are a lot of reasons that you should NOT go this route.
Power Query is designed to take advantage of query folding when targeted against a database. 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. This means that the majority of the heavy lifting is done at the database, not your workstation. 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.
Why is this an issue? Dynamic parameters use custom lines of code, which can’t be folder. 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. Take a guess as to which one is faster?
But what if I want to Pass Parameters to SQL Queries dynamically?
Listen, I totally get it. I love dynamic queries too. And anyone who knows me is aware that I’m super obsessed with making my data tables as short and narrow as possible. This almost always involves filtering based on parameters that the user needs to pass at run time. So are we dead in the water? No, not by a long shot.
How to Pass Parameters to SQL Queries – Method 1
I’m a big fan of query folding, and would encourage you to use it wherever possible. 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. 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. Once you have that, land it in a Connection Only query. And from there use your dynamic parameters to filter it down further to get just what you need.
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. 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. 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. (Yes, I’m aware that nothing is that easy… this is an illustration of concept only!)
The recommended steps would be to do this:
- Create the Staging query
- Connect to the raw database table
- Filter to only the Pacific Division – Server reduces 40m to 10m records
- Filter to only the 2 years of data – Server reduces 10m to 500k records (10/40*2)
- Land this output into a staging query – 500k records total
- Create the parameter table and the fnGetParameter query
- Create a query that references the Staging query and filters the department to the one pulled via the fnGetParameter query
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.
Where Method 1 breaks down
But 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:
- Create the Staging query
- Connect to the raw database table
- Create the parameter table and the fnGetParameter query
- Create a query that references the Staging query and…
- Collects the Division, Date and Department variables
- Filters to only the Pacific Division
- Filters to only the 2 years of data
- Filters to only the selected department
Seems about the same, doesn’t it? Except that this time we can’t take advantage of query folding. To pass a parameter to the database, we have to separate it from the parameters in order to avoid the formula firewall. This means that we break query folding. And this means that Power Query needs to pull in all 40 million records, and process them. Not the server, your Excel instance.
I don’t know how much RAM you have (and don’t care unless you’re on 64 bit), or how many processor cores you have (as Power Query is single threaded), you’re in for a LOOONNNGGG wait… if it doesn’t just tip over on you.
So how do we fully parameterize this stuff?
How to Pass Parameters to SQL Queries – Method 2
The good news that it can be done, the bad news is that you need:
- SQL Skills, and
- An adjustment to the default Power Query load behaviour
Let’s talk SQL Skills
The reason you need SQL skills is that you need to be able to write the most efficient query you possibly can, and pass this into the query when you connect to the database. (Thos italics, as you’ll see, are key.) So, let’s assume that I want to connect to the old AdventureWorks database and pull records from the Sales.SalesOrderHeader table where CustomerID = 29825. You need to be able to write this:
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID='29825'
Why? Because you need to include that query when you’re building/testing your code. It goes in the advanced options, below:
(You may need to trust the Native Database Query to get to the next step.)
So that created the following code for me:
let
Source = Sql.Database("azuredb.powerqueryworkshop.com", "AdventureWorks2012", [Query="SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID='29825'"])
in
Source
Modifying the hard coded query to use dynamic parameters
I then set about fully parameterizing my query using the fnGetParameter query detailed here, and the table shown below:
Here’s the code I cobbled together to do this:
let
//Pull in a values from the parameter table
dbURL = fnGetParameter("Database URL"),
dbName = fnGetParameter("Database Name"),
dbTable = fnGetParameter("Database Table"),
sFilterField = fnGetParameter("Filter Field"),
sFieldValue = Text.From(fnGetParameter("Field Value")),//Create the query
dbQuery = "Select * FROM " & dbTable & " WHERE " & sFilterField & "='" & sFieldValue & "'",//Get the data
Source = Sql.Database(dbURL,dbName,[Query=dbQuery])
in
Source
I won’t go through the fnGetParameter function, as it’s quite well detailed in the blog post, but the key to understand here is that we are pulling a variety of items from the worksheet table, and putting them all together to feed the line starting with dbQuery. This line dynamically sources the database path, database name and the SQL query. Wouldn’t this be heaven if it worked?
Here comes the Formula Firewall again…
But no:
Now if that’s not enough to make you scream… and here’s the issue…
What I really wanted to do was create a power query that connects to the database without declaring the SQL query up front. Something like this:
Source = Sql.Database("azuredb.powerqueryworkshop.com", "AdventureWorks2012")
My thought is that I could load this as a connection only query, then reference it and add the dynamic query afterwards. Unfortunately, I got nowhere. I’m not saying it can’t be done, but I couldn’t figure this out. It seems that the only way to pass a query to the database is to pass it during the initial connection. But doing so, by its very nature, violates the formula firewall.
So how can we get past it?
Bypassing the Formula Firewall
Yes, we can make this work. But doing so involves turning off Privacy settings. I’m not generally a big fan of turning off firewalls, but this one just aggravates me to no end. Normally we turn this off to avoid prompting messages. In this case it just flat out prevents us from running. So let’s kill it:
- Go to Query Settings –> Privacy
- Select Ignore the Privacy Levels and potentially improve performance
And at this point you’ll find out that it works beautifully… for you. You’ll most likely need to have anyone else who uses the file set the above option as well.
Security Settings
The security setting we changed above is workbook specific. Now I’m not recommending you do this, but if you get tired of the privacy settings overall, you can turn them off for all workbooks in the Query Options –> Security tab. The second arrow is pointing at the relevant option:
And how about that? Check out the first option… that one lets you avoid prompting about Native Database Queries. (I seriously have to question why a SELECT query should trip a warning.)
Sample Workbook
Yes, I’ve got one, which you can download here, but it comes with a catch…
I’ve set it up to talk to a SQL Server in order to demo this, as it’s the big databases with query folding that cause us this issue. You might notice that not too many people provide free access to a SQL server since they cost money to run. For this reason, you need a login and password to refresh the solution and really see it work.
As it happens, if you’ve bought a copy of M is for Data Monkey, or attended our PowerQuery.Training workshop, then you’ve already got it the credentials. (They are on page 66 of the book.) And if you haven’t… what are you waiting for? 🙂
![SNAGHTML1590e741[4]](https://www.excelguru.ca/blog/wp-content/uploads/2016/04/SNAGHTML1590e7414.png "SNAGHTML1590e741[4]")
![SNAGHTML1591b06d[5]](https://www.excelguru.ca/blog/wp-content/uploads/2016/04/SNAGHTML1591b06d5.png "SNAGHTML1591b06d[5]")
