Replace SQL query parameter in a Excel power query

For this to work, you need to set up three different parts:

1) A parameter table in an Excel sheet

2) Changes to the advanced editor in PowerQuery

3) A macro to refresh the PQ when any cells in the parameter table are changed

1) Excel Table

You can see I included a column called param which can hold a Parameter name to help keep straight which parameter is which.

2) PQ Advanced Editor

let
    ParamTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Param = "'" & Text.From(ParamTable[value]{0}) & "'",
    Source = Sql.Database("IP Address", "Database Name", [Query="Select * from weeks#(lf)where date >= '2018-01-01' and date < " &Param])
in
    Source

Equivalent alternative: (Difference in location of variable used in SQL query.)

let
    ParamTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Param = "'" & Text.From(ParamTable[value]{0}) & "'",
    Source = Sql.Database("IP Address", "Database Name", [Query="Select * from weeks#(lf)where date < " &Param & " and date >= '2018-01-01'"])
in
    Source

Alternative Variable Type: (If dealing with numbers, the string markers ' aren't required)

let
    ParamTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Param = Text.From(ParamTable[value]{0}),
    Source = Sql.Database("IP Address", "Database Name", [Query="Select * from weeks#(lf)where cnt < " &Param & " and date >= '2018-01-01'"])
in
    Source

Explanation:

After pulling the Parameter Table into the PQ Query (ParamTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]), the columns can be accessed by column name [value] and the rows by a zero-index number {0}. Since I was pulling in a date-value. I needed to convert it to a string value I could insert into the SQL Query -- thus the Text.From() and the appended ''s to the ends (SQL marks strings with single ' rather than the double ")

Since I named the variable Param, to use it in the string, I substituted &Param for the value which had originally been there.

2.1 Power Query's Value.NativeQuery

let
    ParamTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Param = ParamTable[value]{0},
    Source = Value.NativeQuery(Sql.Database("IP Address", "Database Name"), "Select * from weeks where date < @dateSel and date >= '2018-01-01'",[dateSel = Param])
in
    Source

Alternative Formatting:

let
    ParamTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Param = ParamTable[value]{0},
    Source = Sql.Database("IP Address", "Database Name"),
    Data = Value.NativeQuery(Source, "

Select * from weeks
where date < @dateSel and date >= '2018-01-01'

",[dateSel = Param])
in
    Source

Notes:

When using Value.NativeQuery(), you can pass a date or datetime value directly in as a variable without having to include the single apostrophes.

Sometimes splitting the data retrieval into a Source step and a NativeQuery step can help with PQ's sporadic firewall issues.

3) Macro

This works for a simple check if anything in the table has changed, then runs the refresh. You will need to make sure this is placed in the correct module. Items you will need to change are:

• Sheet1 is the codename of the worksheet with the parameter table.
• "Table" is the name of the Parameter table
• "Query - Query1" is the name of the connection to be refreshed

Note: Query1 is the name of the query. Default names for connections are usually Query - & query name

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Sheet1.ListObjects("Table1").DataBodyRange) Is Nothing Then
        ThisWorkbook.Connections("Query - Query1").Refresh
    End If
End Sub