Programmatically change data sources in a Power BI file

[SettlerOfCatan]

Hello everybody,

In the replies to this four-years-old question, the user @v-shex-msft wrote that "....power bi [does] not support to direct modified the datasource without open the pbix file...".

Is it now, after four years, possible to programmatically change, in the best case with PowerShell, the data sources in a Power BI file without opening the Power BI file?

If not, I ask politely to explain in more detail with multiple screenshots how I can use the solution by @v-shex-msft.

I am quite new to Power BI and I could not apply the suggested solution.

With best regards from my small island Catan

SetterOfCatan

[v-yingjl · Accepted solution]

Hi @SettlerOfCatan ,

Currently as far as I know, Powershell could only change the connection strings for the data source in Power BI Destkop Report Server version, not public version, refer:

Change data source connection strings in Power BI reports with PowerShell - Power BI Report Server 

In addition, for the previous workaround, create a xml file to store the server name, database name etc. ,in the sampe connect to sql server.

Then open Power Query editor, create a new blank query and use the query:

let
    xmlTable = Xml.Tables(File.Contents("C:\Users\Admin\Desktop\test.xml")),
    server=xmlTable[Server]{0},
    database=xmlTable[Database]{0},
    Source= Sql.Database(server,database)
in
    Source

It would remind you to edit credentials to connect:

Check your data source credentials to continue, it would remind the privacy level, ignore or keep the public both.

Now it should connect to the corresponding database in Power Query.

After these steps, you can change the datasource without open the pbix file.(After you modified the config file, you should refresh it at pbix report to get the newest data)

Best Regards,

Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.