Power Query (M) – Passing Parameters dynamically to a SQL Server Query
I had a requirement where the client wanted the capability to decide how much data to load from a SQL Server Query (TSQL). This was so that they could limit the dataset returned, as at times they did not need all the data.
So below I demonstrate how to achieve this.
NOTE: This will be slightly advanced because I had to manually add some code in the Advanced Editor in Power Query.
Example:
-
Create a Parameter which will define how many days to go back.
- In this example, it is going to be 31 days.
- Within the TSQL Query use the parameter so that it will only return 31 days’ worth of data.
Creating the Parameter
The first step is to create the required parameter.
-
As you can see below I created the parameter with the following details.
- The one thing to note here is that I set the Type to Text, this is to ensure that it will be passed correctly.
TSQL Query that I used
Below is the starting TSQL Query that I first used before modifying it the Advanced Editor
-
Below is what my TSQL Query looked like when I wrote it and tested it SQL Server Management Studio (SSMS)
-
One of the things to note above is that I purposely put the SELECT Date, MatID on the same line.
- This is because when I put in new lines or tabs in SSMS, it is represented with additional characters in the Advanced Editor in Power Query as shown below.
- So the above adds in additional complexity that is avoided by putting in our variable at the start of the TSQL Query and on one line.
Modifying the TSQL Query in the Query Editor
In the steps below I will show you how I modified the TSQL Query in the Query Editor.
- I went into the Query Editor and clicked on my table named “Query1”
- I then clicked on Advanced Editor, to bring up the Advanced Editor.
-
This is what it looked like originally.
-
Now what I did was to create a new input into my M Code by putting in the following below.
DaysGoingBack = #”Days Back”,
- So now the code looks like the following below.
- What the above is doing is I am taking my initial parameter that I created earlier and putting it into a value in this table.
-
Next I am going to use the above DaysGoingBack and modify my TSQL Query to use this value.
-
So this is what the M code looked like before I made the change.
Source = Sql.Database(“ServerName”, “DatabaseName”, [Query=”Declare @DaysBack as Int Set @DaysBack = 31 SELECT Date, MatID#(lf)FROM
-
And what I then did was when you want to pass this value into your TSQL Query in the Advanced Editor (M) you have to put it in as the following, which is highlighted in Blue below.
Source = Sql.Database("ServerName", "DatabaseName", [Query="Declare @DaysBack as Int Set @DaysBack = ' " & DaysGoingBack & " ' SELECT Date, MatID FROM [Database].[dbo.[ProductionData] as LH with (nolock) left join [Database].[dbo].DIMMaterial] as M with (nolock) on LH.MatID = M.[MaterialID] WHERE Date >= GetDate()-@DaysBack Option (Fast 10000)
-
-
NOTE: In order to add a value to a TSQL Query it uses the following syntax.
‘ ” & ValueName & ” ‘
- Then I clicked Done in the Advanced Editor.
Testing the Parameter
Now I can test the parameter and see if it will work as expected.
-
With the current parameter set to 31, and the current date being 31 Aug 2017, the starting date that I should see in the dataset should be 8/1/2017
-
Now if I change the parameter to 10, and the current date being 31 Aug 2017, the starting date that I should see in the dataset should be 8/23/2017
Conclusion
As you can see above I have demonstrated how to create a parameter that can then be passed through to a TSQL Query, which will then make the TSQL query dynamic.
If you have any questions or comments please leave them in the area below.
I believe this would not support query folding correct?
Yes that is correct, because it is using a custom TSQL Query and not a query from a database view.
Nice blog Gilbert,
My 2 cents: it depends on how you look at it I guess 🙂
The query itself will be folded, but any other steps you add in the Query (or advanced) editor will not. That’s the same with every native database query (where you write your own T-SQL) you use in PQ.
Thanks Nicky, and yes I agree it is very often how you look at it!
Thanks for the comment.
Interesting one.. thanks for sharing
[…] Gilbert Quevauvilliers has an interesting solution to a common problem: […]