Pass parameter from Excel to SQL in PowerQuery-CodePudding

I want to set local variables or pass parameters from Excel to SQL. I've found similar questions, but all referred to old versions of Excel and/or the answers showed how to filter or manipulate output from a generic SQL query in the Power Query Editor, rather than pass a parameter or modify the SQL, so that the SQL Server supplies data in the needed form.

I'm building a large Microsoft Excel spreadsheet that depends on ten different SQL queries, all against a common SQL Server database. Excel and SQL Server are installed on my laptop and are current versions (as of 16 Mar 2022). All ten queries share a common date restriction, imposed in the WHERE clauses of the queries. The tables accessed and the form of output are very different, so there is no easy way to combine the ten queries into a single query. The queries contain multiple levels of aggregation (e.g. SUM(...)) so I need to restrict the records accessed prior to aggregation and passing results from the query back to Excel.

As currently written, each query begins by setting two date values in local variables. For example,

 DECLARE @BEGIN_DATE AS smalldatetime;  
 DECLARE @END_DATE AS smalldatetime;
 @BEGIN_DATE = CAST('2021-03-01 00:00' AS smalldatetime);
 @END_DATE = CAST('2021-03-02 23:59' AS smalldatetime);

Every one of the ten queries includes a line in the WHERE clause similar to

WHERE
    PickUpDate BETWEEN @BEGIN_DATE AND @END_DATE

Every query will use the same pair of dates. However, the column filtered (PickUpDate above) changes from one query to the next.

As it is, I have to manually edit each of the ten queries to change the two dates--twenty edits in all. This is time-consuming and error-prone. Ideally, I'd like to set the date range in the spreadsheet, in a pop-up dialog box, or any other convenient way and pass the dates to the SQL queries. Then by selecting Data > Refresh All in Excel, update all my tables at once.

Is this possible, and if so, how does one do it?

CodePudding user response：

You can reference a table on a sheet from Power Query and integrate values from that table into your other queries. Eg if ParameterTable is a single-row table on some worksheet with a column called "StartDate", something like

let
    theDate = Date.From( Record.Field(Table.First(ParameterTable),"StartDate") ),
    Source = Sql.Databases("localhost"),
    AdventureWorksDW2017 = Source{[Name="AdventureWorksDW2017"]}[Data],
    dbo_DimDate = AdventureWorksDW2017{[Schema="dbo",Item="DimDate"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimDate, each [FullDateAlternateKey] = theDate )    
in
    #"Filtered Rows"

for M query folding, or

let
    theDate = Date.From( Record.Field(Table.First(ParameterTable),"StartDate") ),
    sql = "
      select *
      from dimDate
      where FullDateAlternateKey = '" & Text.From(theDate) & "'
    ",
    Source = Sql.Database("localhost", "adventureworksdw2017", [Query=sql])
in
    Source

for dynamic SQL.