programmatically change dataset SQL statement in power bi

1
votes

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

2
powerbipowerbi-embeddedpowerbi-datasource
Can you be more specific about how you want to modify the SQL statement? – Alexis Olson
This may point you in the right direction: stackoverflow.com/questions/29322381/… – Alexis Olson
@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

2 Answers

0
votes

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.

0
votes

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.