2

Is it possible to change SQL statement of the dataset via API call?

My Scenario: I have data in multiple tables in SQL Server. I have created a SQL query with joins to fetch the required data. I created a SQL server dataset by providing that query in the SQL Statement section and published it on the Power BI workspace. Now, I want to modify that SQL Statment programmatically.

I want to import this same .pbix file to create different datasets. The Idea is to use import date set api to import this dummy dataset and then programmatically change the db source and the SQL Statment, to customizes it for my different report need.

Any pointer or help is much appreciated.

enter image description here

Prathako
  • 91
  • 1
  • 8
  • Can you be more specific about how you want to modify the SQL statement? – Alexis Olson Oct 25 '18 at 19:45
  • This may point you in the right direction: https://stackoverflow.com/questions/29322381/excel-2013-power-query-sql-query-with-a-dynamic-parameter/ – Alexis Olson Oct 25 '18 at 19:49
  • @AlexisOlson, I want to modify SQL Statement possibly via REST Calls, or if there is another way programmatically. I don't want to do it manually from Power BI Desktop. – Prathako Oct 25 '18 at 21:07

2 Answers2

0

At of this writing, this is not supported by the Power BI REST API.

Possible workaround: Given you're using SQL Server, I'd suggest you create a VIEW in SQL Server with the statement you defined in your Power BI report, and change your report to point to that view instead.

Then, to modify the SQL statement, you just have to ALTER the view in the database.

C. Augusto Proiete
  • 24,684
  • 2
  • 63
  • 91
  • Thank you for your response. I cannot possibly create a view for this scenario. I have updated my problem statement with my complete scenario. – Prathako Oct 25 '18 at 21:10
  • @Prathako As you can see in the [Power BI REST API docs](https://learn.microsoft.com/pt-br/rest/api/power-bi/), there's no API that allow you to change the SQL Statement. The PBIX format is a closed file format, therefore there's no supported way to update the SQL Statements programmatically today. I suggest you add a feature request at the [Power BI Ideas](https://ideas.powerbi.com/forums/265200-power-bi-ideas) – C. Augusto Proiete Oct 27 '18 at 21:13
0

For server name and database name, you can simply use parameters. Click the button to the left of the field to do this. You can make some changes in the query using parameters too, but this isn't very flexible. This can be done by defining text parameter and using it in in the M statement associated with the dataset’s Source step. For more information you may see this article:

https://www.red-gate.com/simple-talk/sql/bi/power-bi-introduction-working-with-parameters-in-power-bi-desktop-part-4/

Then you can use the Rest API to modify parameter values and refresh your datasets. You will need Update Parameters In Group and Refresh Dataset In Group API calls.

Andrey Nikolov
  • 12,967
  • 3
  • 20
  • 32