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

Improve this question
3
  • Can you be more specific about how you want to modify the SQL statement? Commented Oct 25, 2018 at 19:45
  • This may point you in the right direction: stackoverflow.com/questions/29322381/… Commented Oct 25, 2018 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. Commented Oct 25, 2018 at 21:07

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

2 Comments

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 As you can see in the Power BI REST API docs, 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.