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.