Some time ago I posted a technique to pass parameters to Power Query via an Excel table.  In a comment on that post, Anand asked “Can I pass the parameter to a SQL Table as well?”  The question could mean one of two things, but the answer to both is “yes”.

Dynamic Database Queries – Native Queries

The first thing it could mean is where you connect to a SQL database and feed it a native query like this:

SELECT  [ClubActivityTypeCode], [ClubMemberCategoryCode]
FROM  [tblClubFeeBillingSchedules]
WHERE  [ClubActivityTypeCode]='Golf'

And where we’d like to make the “Golf” part in the WHERE clause dynamic.  Power Query gives us code for this step as follows:

Source = Sql.Database("servername", "datbasename", [Query="SELECT  [ClubActivityTypeCode], [ClubMemberCategoryCode]#(lf)FROM  [tblClubFeeBillingSchedules]#(lf)WHERE  [ClubActivityTypeCode]='Golf'"])

Pretty ugly again, but we can absolutely have it compile if we use our parameter function.  Without using the parameter function at all, it might look like this:

typecode=fnGetParameter(“ActivityType”),

Source = Sql.Database("servername", "datbasename", [Query="SELECT  [ClubActivityTypeCode], [ClubMemberCategoryCode]#(lf)FROM  [tblClubFeeBillingSchedules]#(lf)WHERE  [ClubActivityTypeCode]='” & typecode & “'"])

Notice that we’ve just inserted a variable to hold the value we pulled via the fnGetParameter function, and then replaced the existing Golf term with the variable between quotes, joining it with the & characters on both sides.

One caveat here: In the case of the query above, we’re passing a custom Select query, which gets flagged as a Native Database Query by Power Query.  This has security permission implications, and Power Query won’t even attempt to fold more steps into this query.

Dynamic Database Queries – Folded Queries

And here’s the other option… with a caveat up front: I’m probably going to butcher the explanation of Query Folding.  My intent isn’t to get this perfectly factually correct, but rather to give you a general sense of what’s going on.  (In my impression, us Excel folks don’t actually NEED to understand the inner working of this piece to make use of it.)

So here goes… In contrast to the Native Database Query, we can avoid security prompts and let Power Query take advantage of query folding at the database level (pushing the process back to the database to complete.)  In layman’s terms this means we connect to the database, we pull in the data, then we do some operations.  Anything Power Query can get the database to do for us, it will “fold” into a single query.  And it will continue to fold more steps in until you try to do something that the database can’t do.  For that and all subsequent steps, Power Query’s engine takes over.

Dealing with these kind of queries works in exactly the same fashion as what you saw in the original parameter table article.  We connect to the database, then perform subsequent steps in Power Query, like filtering out records.  And we use variables and get fnGetParameter function to feed those variables in to the subsequent query steps.

An example of this (using a different database) is as follows, where the original code is:

Source = Access.Database(File.Contents("D:\Excelguru Courses\_Example Files\pos.accdb")),
_tblChits = Source{[Schema="",Item="tblChits"]}[Data],
FilteredRows = Table.SelectRows(_tblChits, each ([POSCategoryCode] ="Beer"))

And the modified example where we’ve made it dynamic:

category = fnGetParameter("POSCatCode"),
Source = Access.Database(File.Contents("D:\Excelguru Courses\_Example Files\pos.accdb")),
_tblChits = Source{[Schema="",Item="tblChits"]}[Data],
FilteredRows = Table.SelectRows(_tblChits, each ([POSCategoryCode] =category))

In this case we are still making dynamic database queries, we’re just feeding Power Query the info in a way that’s a bit easier for us to construct/review, and Power Query will encourage the database to fold it up and execute a query that is equivalent to SELECT * FROM tblChits WHERE POSCategoryCode = “Beer”.

Which Route Should You Go?

Unless you’re a SQL Ninja, use the latter route.  We know that Power Query won’t fold into the most efficient query in all cases.  On the other hand, providing a native database query as your first step immediately shuts down query folding so that any subsequent steps can’t be folded.  So unless you can write wickedly efficient SQL, you could actually hurt your performance.

Connecting to the database, exploring your tables and using Power Query’s filtering is easier for most Excel pros anyway.  Easier to build, and easier to audit.  So why not go down the path of least resistance?