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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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