Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
SettlerOfCatan
Frequent Visitor

Programmatically change data sources in a Power BI file

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...".

 

SettlerOfCatan_0-1632835098241.png

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

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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:

1.png

 

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

2.png

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.

 

View solution in original post

1 REPLY 1
v-yingjl
Community Support
Community Support

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:

1.png

 

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

2.png

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.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.