0

Is there mechanism for online generation of SQL statement for Microsoft Power BI Desktop ODBC data source? I.d. maybe Power BI has some events attached to the datasource, e.g. beforeOpen, beforeRefresh and I can write Visual Basic VBA (DAX? M?) code in the handlers of those event and with this code I can modify the SQL statement?

My question is connected with How to use parameters (e.g. for Firebird) in the SQL statement (optional) that defines ODBC data source for Power BI Desktop - it is quite hard to ignite use of parameters with ODBC and Firebird and hence it is my thought to avoid parameters at all and istead generate SQL code completely and then this code-generating VB code can read parameters (current date, from user input) and adjust generted SQL accordingly.

Additional Information I can import data from the static query and then Power BI created query components in the Model pane. When I choose 'Edit Query' menu item/command, then the Power Query editor opens and I have the opportunity to update the M code which is:

= Odbc.Query("dsn=MY_ODBC_SOURCE", "select s.sale_date, s.amount from sales s where s.sale_date>='01.07.2021'")

I can edit there the sale date parameter and refresh query data.

So, my question boils down to the other question: can I create button in Power BI, that does those 2 things: 1) programmatically edit the Odbc.Query("") source code (by passing parameter values from the controls of the Power BI form); 2) programmatically refresh data?

Additional information Article https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/ details the definition of Power BI parameters. Maybe those parameters can be accessed from the DAX/M expressions that define ODBC query/data source and that can be edited from the Power Query Editor? Those parameters can simple numerical values, but maybe whole parts of custom SQL can be encoded by such parameters or derivations. Currently, though, I don't see how to use Parameters in Power Query.

TomR
  • 2,696
  • 6
  • 34
  • 87

1 Answers1

1

The overwhelming majority of PowerBI reports would simply import all the data and filter/sort it once it's loaded into the DataSet.

You can manipulate the SQL statement in M by creating a Power Query Parameter, and pasting the parameter value into your SQL statement.

But you must refresh the Data Set for any change in the parameter value to take effect, as the SQL Query only runs during a data refresh. That's why the normal practice is to import all the data and filter it in the DataSet.

You can interactively filter the source data if your Data Set is in DirectQuery mode, but you have to be using a data source that supports DirectQuery.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67