Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Programmatically change data sources in a Power BI file

Hello everybody,

 

In the replies to this four-years-old question, the user Anonymous 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 Anonymous.

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

 

 

With best regards from my small island Catan

SetterOfCatan

Need Help
Show and Tell
Tutorial Requests
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    Hi Anonymous ,

    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.

     

1 Reply

  • v-yingjl's avatar
    v-yingjl
    Community Support
    4 years ago

    Hi Anonymous ,

    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.