Use Power Query inputs as parameters in SQL

[MiaSunshine]

Hi, 

I would like to use some parameters in Power Query to pull a few SQL queries as data source for Power BI. Those queries are pulling different data but all for one certain date range. Is it possible to use a hardcoded input date for the date range? We don't have to change it frequently, so a few pop-ups to confirm on refreshing each time isn't the best choice. Or the only way is to change all the SQL queries one by one when we need to reset the start date and end date?  

Thank you! 

[Nasif_Azam · Accepted solution]

Hey @MiaSunshine ,

Yes, it’s absolutely possible to define hardcoded input dates (like a StartDate and EndDate) as Power Query parameters and then use those parameters in your SQL queries, avoiding the need to edit every query manually.

Step 1: Create Parameters in Power Query

1. In Power BI Desktop, go to Home > Manage Parameters > New Parameter.

2. Create a parameter named StartDate:

   • Type: Date

   • Current Value: e.g., 1/1/2025

   • Keep it non-mandatory and without prompting.

3. Repeat for EndDate.

Step 2: Reference Parameters in Your SQL Queries

SELECT *
FROM YourTable
WHERE TransactionDate BETWEEN @StartDate AND @EndDate

But Power BI doesn't support direct parameter injection like that. Instead, do this:

1. Use Value.NativeQuery in Power Query M language:

   let
       StartDate = Date.ToText(Parameters[StartDate], "yyyy-MM-dd"),
       EndDate = Date.ToText(Parameters[EndDate], "yyyy-MM-dd"),
       Source = Sql.Database("YourServerName", "YourDatabaseName"),
       Result = Value.NativeQuery(
           Source,
           "SELECT * FROM YourTable WHERE TransactionDate BETWEEN CAST(@StartDate AS DATE) AND CAST(@EndDate AS DATE)",
           [StartDate = StartDate, EndDate = EndDate]
       )
   in
       Result

Replace "YourServerName", "YourDatabaseName", and "YourTable" accordingly.

Step 3: Apply the Same Parameters to All Queries

Every query that depends on date range filtering should reference the same StartDate and EndDate parameters, so when you update the parameter once, it affects all queries.

Things to remember:

• Avoid using #date(...) or 'YYYY-MM-DD' directly in your SQL. Instead, use Date.ToText(StartDate, "yyyy-MM-dd") for consistent formatting.

• If your database doesn't support named parameters (@StartDate), you can concatenate the values directly into the SQL string but this loses query folding and can be risky. Safer to use Value.NativeQuery.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[Nasif_Azam · Accepted solution]

Hey @MiaSunshine ,

Yes, it’s absolutely possible to define hardcoded input dates (like a StartDate and EndDate) as Power Query parameters and then use those parameters in your SQL queries, avoiding the need to edit every query manually.

Step 1: Create Parameters in Power Query

1. In Power BI Desktop, go to Home > Manage Parameters > New Parameter.

2. Create a parameter named StartDate:

   • Type: Date

   • Current Value: e.g., 1/1/2025

   • Keep it non-mandatory and without prompting.

3. Repeat for EndDate.

Step 2: Reference Parameters in Your SQL Queries

SELECT *
FROM YourTable
WHERE TransactionDate BETWEEN @StartDate AND @EndDate

But Power BI doesn't support direct parameter injection like that. Instead, do this:

1. Use Value.NativeQuery in Power Query M language:

   let
       StartDate = Date.ToText(Parameters[StartDate], "yyyy-MM-dd"),
       EndDate = Date.ToText(Parameters[EndDate], "yyyy-MM-dd"),
       Source = Sql.Database("YourServerName", "YourDatabaseName"),
       Result = Value.NativeQuery(
           Source,
           "SELECT * FROM YourTable WHERE TransactionDate BETWEEN CAST(@StartDate AS DATE) AND CAST(@EndDate AS DATE)",
           [StartDate = StartDate, EndDate = EndDate]
       )
   in
       Result

Replace "YourServerName", "YourDatabaseName", and "YourTable" accordingly.

Step 3: Apply the Same Parameters to All Queries

Every query that depends on date range filtering should reference the same StartDate and EndDate parameters, so when you update the parameter once, it affects all queries.

Things to remember:

• Avoid using #date(...) or 'YYYY-MM-DD' directly in your SQL. Instead, use Date.ToText(StartDate, "yyyy-MM-dd") for consistent formatting.

• If your database doesn't support named parameters (@StartDate), you can concatenate the values directly into the SQL string but this loses query folding and can be risky. Safer to use Value.NativeQuery.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[MiaSunshine]

    Result = Value.NativeQuery(
        Source,
        "SELECT * FROM YourTable WHERE TransactionDate BETWEEN CAST(@StartDate AS DATE) AND CAST(@EndDate AS DATE)",
        [StartDate = StartDate, EndDate = EndDate]
    )

Thank you @Nasif_Azam This is really helpful. 

I tried the code and it works. Usually i will use Sql.Database("server name", Db name", [Query =" ..."]) for Sql , and here I didn't get the last part, startdate = startdate in the brackets, what does that mean, or how could I change it according to different situation? 

[Nasif_Azam · Accepted solution]

Hey @MiaSunshine ,

Glad it worked! 😊 To answer your follow-up question:

[StartDate = StartDate, EndDate = EndDate]

This part of the Value.NativeQuery function is a parameter mapping. It tells Power Query which M values should be substituted for the SQL query’s parameters (those prefixed with @, like @StartDate and @EndDate).

• Left side (StartDate and EndDate): These match the names of the SQL parameters in your query (@StartDate, @EndDate).

• Right side (StartDate = StartDate): This is saying, “use the M variable named StartDate as the value for the SQL parameter @StartDate.”

So if you had:

let
    StartDate = Date.ToText(#date(2025, 1, 1), "yyyy-MM-dd"),
    EndDate = Date.ToText(#date(2025, 12, 31), "yyyy-MM-dd"),
    ...

Then [StartDate = StartDate] is binding the M value "2025-01-01" to the SQL placeholder @StartDate.

 

If you rename your M variables or SQL parameters, you just need to update the mapping like this:

[SQLParamName = MVariableName]

Example:

let
    sd = Date.ToText(#date(2025, 1, 1), "yyyy-MM-dd"),
    ed = Date.ToText(#date(2025, 12, 31), "yyyy-MM-dd"),
    Source = Sql.Database("YourServer", "YourDB"),
    Result = Value.NativeQuery(
        Source,
        "SELECT * FROM MyTable WHERE Date BETWEEN @s AND @e",
        [s = sd, e = ed]
    )
in
    Result

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam