Dynamic Excel SQL report

Create a stored procedure with the same number of parameters that are needed to be refreshed.

Start with creating the parameter fields in Excel.

Then, we will highlight them and assign them a name, in this instance: “GetValue_1”

Next, we will add the query from SQL Server, as described in the screenshot below.

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'

When ready, click, OK. And we can click the “Load” button.

It will load the results into a new sheet.

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.

Note, if the above window is missing, go to Data > and click “Show Queries”.

After clicking Edit, it will open the Power Query window.

Click the “Advanced Editor” button to see the code. Note, we will use this the editor window very frequently in this tip.

Below we can see the code being uses for the procedure call.

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.

1. A block of code most have “LET” and “IN”.
   1. The “LET” section will include all our steps in will be evaluated in sequence
   2. The “IN” section is used for outputting the result
2. 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.
3. The M language evaluation concepts:
   1. 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.
   2. 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.

We will click the “Advanced Editor”.

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	

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.

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.

Now that we have the Sell Date, we can get the End Date value the same way.

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 &"' "	

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.

Next, click on “Close & Load” and from the drop-down menu, select “Close & Load To…”

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.

Then we should get the results, as in the screenshot as shown below.

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.

Note, after a hitting refresh it might ask you to approve to run the query. Just click Run.