Pass parameters to SQL query (sql instructions from ODBC)

[powerbricco]

Dear Forum,

in PowerBI through a ODBC connection i am connecting to a mySql db and the following is my sql query instruction.

What i want is pass parameteres to this sql instruction instead of hardcoding the dates.

Is it possible to do this?

Thank you a lot

[v-sgandrathi · Accepted solution]

Hi @powerbricco,

You can achieve this by using Power Query parameters and incorporating them into your native SQL statement, allowing filtering to occur directly in MySQL rather than within Power BI. This method ensures only the necessary data is retrieved from the source, helping to prevent performance issues that can arise from filtering after import.

To set this up, create parameters in Power BI Desktop by navigating to Transform Data -> Manage Parameters -> New Parameter. For example, you might create StartDate and EndDate parameters with a data type of Date or Date/Time. These will serve as dynamic inputs for your query.

Next, open the Advanced Editor for your ODBC query and update the SQL string to reference these parameters. In Power Query (M), you can insert the parameter values into the SQL statement and format them appropriately for MySQL. When the query runs, Power BI rebuilds the SQL with the current parameter values and sends it to the database.

This approach applies the date filter at the source, so MySQL returns only the relevant data, resulting in faster refresh times and avoiding unnecessary data loads. It is effective for refresh-time filtering in Import mode. For end-user runtime parameters in Power BI Service, DirectQuery or a stored procedure would be required, but for refresh scenarios, this method is recommended.

Thank you.

[powerbricco]

Dear @rohit1991, applying filters after import is terribly slowing the whole process.
You were saying it is possible to use Power Query parameters and reference them in the SQL statement. How is this possible?

Thanks for your help

[rohit1991]

Hii @powerbricco 

The supported approach is to use Power Query parameters and reference them in the SQL statement (or apply filters after import). For true runtime parameterized queries, you must use DirectQuery with supported connectors or handle parameters via stored procedures (where supported).