By: Ohad Srur | Comments (11) | Related: > Microsoft Excel Integration
Problem
Microsoft Query allows us to retrieve data from external sources into Excel easily, which can be very handy if we want to provide an ad-hoc report to our users. All we need is to copy-paste our T-SQL query code and hand the Excel file over to our users, who can refresh the dataset at any time.
However, sometimes we are required to add parameters to our report, and we usually gravitate to a different tool like Reporting Services, Power BI, Crystal Reports, etc. Not any more, with the power of the M language, we can add parameters into our Excel report and use them for our query. In this tip, I will show you how it's done.
Solution
In our example, I’ll use the AdventureWorks2017 database, and I’ll create a simple stored procedure to query the "Product" table between the selling start and sell end dates. Our task in this example is to pass different sell dates to the stored procedure, and our report will refresh accordingly, allowing the user to manipulate the dates parameters as they wish without modifying the query.
USE [AdventureWorks2017] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Example: exec [AdventureWorks2017].dbo.ProductList '2011-05-01','2012-12-24' CREATE PROCEDURE [dbo].[ProductList] @SellStartDate as Date, @SellEndDate as Date AS BEGIN SET NOCOUNT ON; SELECT PR.[Name] ProductName ,[StandardCost] ,[ListPrice] ,CAST([SellStartDate] AS DATE) SellStartDate ,CAST([SellEndDate] AS DATE) SellEndDate FROM [AdventureWorks2017].[Production].[Product] PR WHERE SellStartDate>=@SellStartDate AND SellEndDate<=@SellEndDate ORDER BY SellStartDate,ProductName END
We will start with creating the parameter fields.
![parameter fields](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.001.png)
Then, we will highlight them and assign them a name, in this instance: "GetValue_1"
![get value](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.002.png)
Next, we will add the query from SQL Server, as described in the screenshot below.
![sql server database](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.003.png)
And fill in the required information for server and database. In the statement box, we will copy-paste an example.
exec [AdventureWorks2017].dbo.ProductList '2011-05-01','2012-12-24'
![sql server database](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.004.png)
When ready, click, OK. And we can click the "Load" button.
![adventure works](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.005.png)
It will load the results into a new sheet.
![product name](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.006.png)
Next, we need to modify the M language to find out the line of code used to generate the database call.
Therefore, we need to edit the query.
![workbook queries](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.007.png)
Note, if the above window is missing, go to Data > and click "Show Queries".
![show queries](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.008.png)
After clicking Edit, it will open the Power Query window.
![query editor](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.009.png)
Click the "Advanced Editor" button to see the code. Note, we will use this the editor window very frequently in this tip.
![transform](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.010.png)
Below we can see the code being uses for the procedure call.
![advanced editor](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.011.png)
We will make a note of this line of code, as we will use it later.
Following the below steps, we will create the necessary M code. Unless you are an expert in M, I suggest you test each line of code until you achieve the desired M code. The best way to do that is to output the result variable of each step.
Please note the following if you are unexperienced with M.
- A block of code most have "LET" and "IN".
- The "LET" section will include all our steps in will be evaluated in sequence
- The "IN" section is used for outputting the result
- It is recommended that each operation have its own line of code, and it has to include a variable name at the beginning. And it must terminate with a comma unless it’s the last line in the "LET" block.
- The M language evaluation concepts:
- M is using "Lazy Evaluation", which means each line of code is evaluated as needed. If the line of code is not required for the output, it will not be calculated.
- All other expressions are using "Eager Evaluation", which means that they are evaluated immediately when encountered during the evaluation process.
In order to build the M code, we will create a Blank Query and slowly build the required code.
![show queries](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.012.png)
We will click the "Advanced Editor".
![transform](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.010.png)
In the following code, we will pull the parameter values from the spreadsheet into a variable.
let Source = Excel.CurrentWorkbook(){[Name="GetValue_1"]}[Content] in Source
![query](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.013.png)
![excel](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.014.png)
Next, we need to pull the date value into a variable. If we right-click on the Date field and select "Drill Down", we will get the required code. To view the code, click on the Home tab > Advanced Editor.
![advanced editor](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.015.png)
![convert](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.016.png)
![advanced editor](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.017.png)
Now, we need to convert the date to text with the format "yyyy-mm-dd". To do so, we can apply the function "ToText" from the DateTime library, more details are available here.
![current workbook](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.018.png)
![date time](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.019.png)
Now that we have the Sell Date, we can get the End Date value the same way.
![current workbook](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.020.png)
![end date](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.021.png)
Now, let’s create the required stored procedure call as a variable called "query". We can use the same common operators like in Excel to create the required call.
query = "exec ProductList '"& SellDate &"','"& EndDate &"' "
![current workbook](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.022.png)
![product list](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.023.png)
Now, let’s copy the query line generated by M from Query 1. We need to modify it accordingly, instead of passing the procedure call, we will pass the "query" variable.
let Source = Excel.CurrentWorkbook(){[Name="GetValue_1"]}[Content], SellDate = DateTime.ToText(Source{0}[SellDate],"yyyy-MM-dd"), EndDate = DateTime.ToText(Source{0}[EndDate],"yyyy-MM-dd"), query = "exec ProductList '"& SellDate &"','"& EndDate &"' ", target = Sql.Database("ohad\dbtesting", "AdventureWorks2017", [Query=query]) in target
Now, click "Done", and you will be required to approve the following screens for the Privacy level.
![sql database](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.024.png)
![privacy levels](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.025.png)
![power query editor](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.026.png)
Next, click on "Close & Load" and from the drop-down menu, select "Close & Load To..."
![power query editor](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.027.png)
We can load the data to the same worksheet, by selecting "Existing worksheet" and specifying the cell where we would like the table to start.
![load to](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.028.png)
Then we should get the results, as in the screenshot as shown below.
![sell date](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.029.png)
Now, if we want to change the parameters, we can modify the dates, and go to Data > Refresh All to refresh the data. Or, hit Ctrl + Alt + F5 on the keyboard.
![show queries](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.030.png)
Note, after a hitting refresh it might ask you to approve to run the query. Just click Run.
![native database query](/tipimages2/6411_how-to-add-parameters-excel-native-database-query.031.png)
Additional Resources
- Find out more about the different M functions - https://docs.microsoft.com/en-us/powerquery-m
- A similar solution without using the M language, and using VBA script instead - https://www.mssqltips.com/sqlservertip/3436/passing-dynamic-query-values-from-excel-to-sql-server/
- A list of all supported external data sources -https://support.office.com/en-us/article/import-data-from-external-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a
Next Steps
- You can add automatic refresh every time the parameters are changed, using a VBA script.
- Download the example here.
About the author
![MSSQLTips author Ohad Srur](/images/Ohad-Srur.png)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips