Use Power Query inputs as parameters in SQL

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

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