Pass Parameters to SQL Queries

2016-04-28
Ken Puls
Categories: Excel, Power BI, Power Query

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 can be downloaded 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 (now Power Query Academy), 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? 🙂

40 thoughts on “Pass Parameters to SQL Queries”

Bill Szysz on April 29, 2016 at 2:41 pm said:

Great post, Ken!
Thanks.
sam on April 30, 2016 at 10:19 pm said:

@Ken - Always ignore privacy - is not available in Excel 2013 is it ?
Right now it seems to be available in Power BI Desktop
Ken Puls on May 1, 2016 at 9:05 pm said:

Hey Sam,

It should be. I'm 99% sure that all the settings are the same once you updated to the latest version. (Don't have 2013 on this machine to check though.)
sam on May 2, 2016 at 2:23 am said:

@Ken - I checked - Its seems to be available on Office 365 Pro Plus (2016) - "First release" and Power BI Desktop

Its not their on the Office 2013 MSI despite the latest release of the addin (502) nor in Get and Transform in Office 2016 MSI despite running the latest updates in "Current Channel"

MS is recreating the .net Hell with Power Query addin - where there are so many out of sync versions of the same thing floating around.
Ken Puls on May 2, 2016 at 7:00 am said:

Odd. I'm suprised on the 2013 one, but actually not about the 2016 MSI.

Part of Microsoft's strategy is to deliver value for their subscription customers, which is why the office 365 subscriptions are updated so frequently. The MSI won't be, for the very reason that it would eliminate the value for the subscription customers. I'm with some MS guys this week so I'll try to confirm, but I believe that the idea is to roll up these updates for MSI customers, but on a less frequent basis.
Dory Owen on May 3, 2016 at 5:00 am said:

Wow, still a lot of hurdles but you don't give up! I always learn from you, Ken. Thanks.

I am always looking for ways to REDUCE my initial query from Salesforce reports due to its 2000 row limit. So parameters are a necessity (as in my last blog post). I may attempt some SOQL querying soon - similar to SQL.

I like the idea of building a list of valid parameters from an initial pre-query.
Charlie Hall on May 3, 2016 at 9:10 am said:

Interesting and useful article

I am now wondering if my current queries are running slow because query folding is not happening because one of my first steps is to reduce the number of records based on a user supplied date. Is there any source you can point to that would provide the background on query folding and when it does or doesn't come into effect?

Thanks
Ken Puls on May 3, 2016 at 9:17 am said:

Hi Charlie,

Rule of thumb is this... it works up to the first line of custom M code you inject. So if you declare your parameters up front, there's no query folding at all. If you connect to the data set, group and sort, THEN have a line to determine the parameter, then filter, it will fold the connection, group and sort only.
Anthony on May 5, 2016 at 1:34 am said:

Hi Ken,
What about the new "Parameters" feature that came in the last PowerBi update from 28-Apr-2016 ?
Ken Puls on May 10, 2016 at 9:54 am said:

Hey Anthony,

So the new Parameters feature works, but only if you're in Power BI Desktop. The query can be set up and the parameters can be changed from the dashboard via a menu, which kicks off a refresh.

There is no way to change the parameter at run time in Power BI Service as of today, which is a bit disappointing.
Pingback: How To Change Data Sources Dynamically In Excel With Power Query | Dick Moffat's Spreadsheet Blog
Pingback: Query folding and dynamic parameters in SQL Queries in Power Query – Ivan Bond's blog
Scott on July 26, 2016 at 9:36 am said:

Hi, Great post. I created a parameter table and function and used it to which was passed to a native SQL query. Everything worked great until I updated a date parameter. The load failed and gives the folowing error:

The query table couldn't be refreshed:
The query 'xxxxxxxx' or one of its inputs was modified in Power Query after this connection was added. Please disable and re-enable loading to the Data Model for this query.
Ken Puls on August 17, 2016 at 9:18 am said:

Usually I can fix this kind of issue by closing Excel (completely, making sure the Excel.exe process is gone), then re-loading the file. The only other fix would be to remove the table from the data model and try again, but that shouldn't be necessary. (In fact, I don't think I've ever had to do that.)
banks on August 30, 2016 at 9:13 am said:

I get the following error when inputting a number Value as my parameter.

DataSource.Error: Microsoft SQL: Conversion failed when converting the varchar value 'XXX' to data type int.
Details:
Message=Conversion failed when converting the varchar value 'XXX' to data type int.
Ken Puls on August 30, 2016 at 10:08 am said:

Somewhere, you're trying to convert a value into an integer and it doesn't like it. Probably the best place to ask on this is in our forum at http://excelguru.ca/forums
banks on September 1, 2016 at 8:47 am said:

I figured it out. I needed to convert the Text To number using a syntax similar to this ' " & Number.ToText(XXX) & " '
Miguel Ulloa on October 27, 2016 at 10:03 am said:

Hi Ken!

Lovely post. I'm starting on PowerBI; however, I've used the good old Microsoft Query for ages. I'm fairly fluent in SQL, so I thought I could just define a parameter in PowerBI and it would replace a bit of code in the SQL statement, before sending it to the server, but that would have been too easy.

I realize that PowerBI may be designed for a slightly diffent purpose, but is there anyway of doing something similar?
Ken Puls on October 27, 2016 at 9:36 pm said:

Hey Miguel,

Yeah, honestly, I'm not totally in love with the Power BI parameters for that reason. If you're working in Excel you can do this by reading from an Excel worksheet (via Power Query), but in Power BI the query pretty much needs to be fully rolled before you call it.
amlan gourav on May 30, 2017 at 1:29 am said:

can this be achieved using ORACLE database ?
Ken Puls on May 31, 2017 at 9:15 pm said:

There is an Oracle connector, so I don't see any reason why not. After that it's just SQL.
shirish on April 19, 2018 at 9:11 am said:

I am getting this error when I join tables and trying to retrive data based on a filter table.

my filter is as below

#"Filtered Rows" = if patName= "All" then Table.SelectRows(Source, each true) else Table.SelectRows(Source, each Text.Contains([name] = patName))

Expression.Error: 1 arguments were passed to function which expects between 2 and 3.
Details:
Pattern=
Arguments=List
shirish on April 19, 2018 at 9:22 am said:

Hi Ken,

it works fine if i give specific value like PQ111(as mentioned in below code); but when i provide PQ then it fails ( as mentioned in above comment's code

#"Filtered Rows" = if patName= "All" then Table.SelectRows(Source, each true) else Table.SelectRows(Source, each ([name] = patName))
Ken Puls on April 19, 2018 at 9:36 am said:

Hi Shirish,

I think we'd want to see some sample data with this to debug it. Could you post in our forum at http://www.excelguru.ca/forums ? Thanks!
shirish on April 20, 2018 at 1:54 am said:

Hi Ken,

Thanks for your reply. I figured it out by myself

incorrect code:
#"Filtered Rows" = if patName= "All" then Table.SelectRows(Source, each true) else Table.SelectRows(Source, each Text.Contains([name] = patName))

correct code:
#"Filtered Rows" = if patName= "All" then Table.SelectRows(Source, each true) else Table.SelectRows(Source, each Text.Contains([name] , patName))

I just needed to change = sign to , at the end :D.

Was struggling with this for 10+ days.
Cindy on April 24, 2018 at 12:16 pm said:

I am trying to connect to a SQL server that I am calling from a parameter and I keep getting this error.
DataSource.Error: Microsoft SQL: Incorrect syntax near '/'.
Details:
DataSourceKind=SQL
DataSourcePath=reg.cja.ca\servmssql;OTS_GTAPP
Message=Incorrect syntax near '/'.
Number=102
Class=15
Catherine Parkinson on July 18, 2018 at 2:24 am said:

Hi, do you know where the Native Database Queries dialogue is in Excel365 ? I'm trying to access the "Require user approval for new native database queries" checkbox and can not find it !
Help !
Ryan on April 17, 2019 at 11:16 am said:

Does the new parameter feature in Excel fix the requirement to ignore Privacy Settings?

Was reading through the comments, and saw that old post.
Ken Puls on April 24, 2019 at 9:22 am said:

@Ryan, maybe, but I doubt it. It because the value is coming from a different "query", I believe it will still cause the same issue.
Ken Puls on April 24, 2019 at 9:23 am said:

@Catherine, yes... go to Data -> Get Data -> Query Options -> Security
Ken Puls on April 24, 2019 at 9:24 am said:

@Cindy, my guess is that your SQL query is not being manifested into the correct text string to be passed to the database. Hard to say without seeing the actual code/result. You may want to try posting in our forum at https://www.excelguru.ca/forums to see if someone can help you troubleshoot
Charles Philpott on April 29, 2019 at 10:18 am said:

Your post appears to be the gift that keeps on giving. Thank you. I was able to apply it to enable retrieving records from multi-million record tables by narrowing date range. For what it's worth, here is a recent successfully applied example against SAP Hana running against a calculation view I created:
I had a similar situation hitting very large SAP table Data sources (KONV - Condition Types, and OICQ7 Condition Type Formulas. KONV is in excess of 50 million records so dynamically filtering a narrow date range on the SAP side was highly preferred performance wise. I built a parameter table in Excel with a Begin and End date. To facilitate the need of using multi-quotation marks in the query syntax, I also added a (") Quote symbol to my Excel parameter table:
Parameter Value
Begin_Date '20190301'
End_Date '20190331'
Quote "

I segmented the query into a series of build statements and then combined them to form the SQL Query that would hit SAP HANA. Once the results were returned, I continued just using the regular Power Query qroup by function to further consolidate the results:

let
Build0 = Text.From(fnGetParameter("Quote")),
Build1 = "Select ""Condition"", ""Item_No"", ""FKDAT"", ""IV01_Formula_Description"", ""ZP01_Formula_Description"", ""ZP03_Formula_Description"", ""ZZP2_Formula_Description"", ""ZZQE_Formula_Description""#(lf)#(lf)FROM ""_SYS_BIC"".""Master_Data/KONV_CONDITIONS_W_OICQ7_FORMULAS"" #(lf)GROUP BY ""Condition"", ""Item_No"", ""FKDAT"", ""IV01_Formula_Description"", ""ZP01_Formula_Description"", ""ZP03_Formula_Description"", ""ZZP2_Formula_Description"", ""ZZQE_Formula_Description""",
Build2 = " Having ",
Build3 = Build0&"FKDAT"&Build0,
Build4 = " >= ",
BeginDate = Text.From(fnGetParameter("Begin_Date")),
Build5 = " and ",
Build6 = Build0&"FKDAT"&Build0,
Build7 = " <= ",
EndDate = Text.From(fnGetParameter("End_Date")),
Dbquery1 = Build1&Build2&Build3&Build4&BeginDate&Build5&Build6&Build7&EndDate,

Query1 = SapHana.Database("atxhanaprd:30015", [Query=Dbquery1]),
#"Grouped Rows" = Table.Group(Query1, {"Condition", "Item_No"}, {{"IV01_Formula_Description", each List.Max([IV01_Formula_Description]), type text}, {"ZP01_Formula_Description", each List.Max([ZP01_Formula_Description]), type text}, {"ZP03_Formula_Description", each List.Max([ZP03_Formula_Description]), type text}, {"ZZP2_Formula_Description", each List.Max([ZZP2_Formula_Description]), type text}, {"ZZQE_Formula_Description", each List.Max([ZZQE_Formula_Description]), type text}})
in
#"Grouped Rows"
URCA on May 14, 2019 at 7:11 am said:

I ' have native level in Power BI , I want to applay a function to remove specific rows on my table like that

#"Lignes filtrées" = Table.SelectRows(#"Type modifié", each ([ARKTSOC] "999")),
#"DEleteit" = Tables.RemoveRows(#"Lignes filtrées")
in
#"Lignes filtrées"
but this did not work.
Help me please.
Ken Puls on May 14, 2019 at 7:54 am said:

Hi URCA, you have a couple of things going on here...

The final #"Lignes filtrées" (after the in) is referring to the first step of the query, ignoring the last one. But that wouldn't work anyway. It looks like you're trying to filter to a certain record, then remove it, but that's not how Power BI works. Instead, you should use your filter and uncheck "999" from the ARKTSOC column, which should do what you're after.
Pingback: Chris Webb's BI Blog: Passing Parameters To SQL Queries With Value.NativeQuery() In Power Query And Power BI Chris Webb's BI Blog
Sean Woods on January 28, 2020 at 7:22 am said:

Ken, great post. Thanks for your insight.

I ran into a similar problem and found a creative solution on another blog post a few days ago. The solution seems quite similar to what you have outlined and wanted to pass along.

https://reevessmith.wordpress.com/2014/08/19/power-query-and-stored-procedures-with-parameters/
Ken Puls on January 28, 2020 at 9:08 am said:

Hi Sean, indeed that's quite similar. 🙂
Sridhar on October 21, 2020 at 4:52 pm said:

Hi Ken,

Thanks for the post. It was helpful in understanding about parameters in Excel.
One issue that i'm facing by following the above steps is that, the parameter value is a numeric value and because i'm closing it with double quotes, its giving me an error given below.
Expression.Error: We cannot apply operator & to types Text and Number.
Could you please help me ? Thanks.
Ken Puls on October 22, 2020 at 1:07 pm said:

Hi Sridhar,

You'll need to convert your value to text using Text.From( yourvalue )
Martin Lucas on November 6, 2020 at 12:50 am said:

Hi Ken

Thank you for this and the linked blog "Building a Parameter Table for Power Query"
This has been an awesome lesson, which I was able to adapt very simply for my needs.
Thank you for taking the time to share.