SQL Dataset Migration using code

[sidhharth_1996]

Hello Everyone,

I am working on a project where i am trying to create powerbi report automatically using python code. Here i am facing an issue that i want to connect the SQL Datasource along with the dataset to powerbi using code, without mannual effort. I have a M-query for this. But i am unable to find any solution for this. I tried to modify the DataModel file in the pbix file but that is binary encoded. And i try to push data using Azure AD registration But still i am facing problem. So any one help me on this.

I want to make this dynamic so that any type of data source can be detected and push to the fabric PowerBI so i can create simantic model and then i can create report using those.

I have m query in below structure it can fetch data in query editor but i want to make it automate through code.

let
Source = Sql.Database("Datasource_host\SQLEXPRESS", "tableau_test"),
dbo_tableau_test_table = Source{[Schema="dbo",Item="Book2"]}[Data]
in
dbo_tableau_test_table

thank you.

[rajendraongole1]

Hi @sidhharth_1996  - Automating the creation of Power BI reports and connecting to SQL data sources programmatically is challenging because the Power BI .pbix file structure is proprietary and not publicly documented.Unfortunately, the Power BI API does not directly support embedding M queries. 

you can try with use Power BI REST API for Automation

suggest some alternative like, 

Azure Data Factory (ADF) can integrate with Power BI datasets and handle dynamic SQL data source connections. You can:

Use ADF pipelines to fetch data from SQL Server.

Push the data to Power BI datasets via the REST API.Automating the M query injection is not fully supported in Power BI APIs.

Power BI REST APIs for embedded analytics and automation - Power BI REST API | Microsoft Learn

Power BI Tabular Editor 3: Automate With A Script – Master Data Skills + AI

[sidhharth_1996]

Thank you for the help.

But i am looking for a solution that can be fetch the data from sql server and upload the dataset to the powerbi and create simatic model automaically what i specified in a json/some other format. So i can create report on top of it.