Load data to excel from SQL procedure with parameters — Power Query

Data > Get Data > From Databas > From SQL Server Database

If you already know Power Query in MS Excel — you ofc can easily work with SQL statements and retrieve data from a database.

But if you need to extract data dynamically, with WHERE clause, or with using any parameter, or to retrieve data using the stored procedure, you can read this article and download an Excel example.

parameter list and values

In a separate sheet create a list of parameters with values. This list you can use in SQL query and then convert this list as table (Ctrl+ T).

Set Table name for example TableVars

Select this table and go to Data > From Table/Range (for import table data in Power Query)

Then create function that returns value of the parameter.

go to Advanced Editor

and copy this code

(ParameterName as text) =>
 let
 ParamSource = #”TableVars”,
 ParamRow = Table.SelectRows(ParamSource, each ([Variable]=ParameterName)),
 Value=
 if Table.IsEmpty(ParamRow)=true
 then null
 else Record.Field(ParamRow{0},”Value”)
in
 Value

set function name fnGetParameters

after that, we can create new blank query again, and when we call function for example like this fnGetParameters(“Server”) with parameter from table Variable name “Server” it will be return his value: localhost\SQLEXPRESS

let
ServerName = fnGetParameters(“Server”),
dbname = fnGetParameters(“dbname”),
TableName = fnGetParameters(“TableName”),
OrderDateKey = fnGetParameters(“OrderDateKey”),

// build a query
 dbQuery = “ Select top 100 * FROM “ & TableName & “ where OrderDateKey = ‘“ & OrderDateKey & “‘ “,
 
 //Get the data
 Source = Sql.Database(ServerName, dbname, [Query=dbQuery])
 
in
 Source

Download these files https://drive.google.com/drive/folders/1wdNXkwNXG7cgmL8yTwWAk-tc_hJ13RYt?usp=sharing

import db.xlsx in database or just change variables data in file:

Data From SQL.xlsx > sheetname: variables