Forum Discussion

JorgeDiego's avatar
JorgeDiego
Icon for Advocate I rankAdvocate I
9 years ago
Solved

Change pbix data source programmatically

Hi everyone,   I want to know wheter is it possible to change the data source from a pbix file programmatically.  I want to use the same report for different customer that use different data bases...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi JorgeDiego,

     

    As you said, power bi not support to direct modified the datasource without open the pbix file, perhaps you can submit your requirement to ideas.


    In addition, you can try to store your connection string to a config file, then write a custom function to analysis this file. 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)

     

    Sample:

     

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

    Xml:

    <config>
<Server>"abc"</Server>
<Database>"edd"</Database>
</config>

    Screenshots:

     

     

    Regards,

    Xiaoxin Sheng

JorgeDiego's avatar
JorgeDiego
Icon for Advocate I rankAdvocate I
9 years ago
Many thanks for your message but noone is helping me. I want to be able to change the datasource without having to open the pbix file. I have been surfing on the internet looking for any solution but I have found nothing. I´m beging to think that it is not possible to do what I want. Thank you anyway
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi JorgeDiego,

 

As you said, power bi not support to direct modified the datasource without open the pbix file, perhaps you can submit your requirement to ideas.


In addition, you can try to store your connection string to a config file, then write a custom function to analysis this file. 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)

 

Sample:

 

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

Xml:

<config>
<Server>"abc"</Server>
<Database>"edd"</Database>
</config>

Screenshots:

 

 

Regards,

Xiaoxin Sheng

  • JorgeDiego's avatar
    JorgeDiego
    Icon for Advocate I rankAdvocate I
    9 years ago

    It could be a good solution. It is not the best, but, currently, the solution I´m looking for doesn´t exist. 

    Many thanks.

  • Sowjanya1's avatar
    Sowjanya1
    Frequent Visitor
    8 years ago

    Could you please elaborate the last part

    Source= //invoke data connection method here

    Bare with me please, bcoz i'm very new to this.

  • kkalyanthaya's avatar
    kkalyanthaya
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Anonymous,

     

    Can this method be used as a way to share templates / content packs to connect to different sources ?

    The history behind this question is here.

    • modelmike's avatar
      modelmike
      Frequent Visitor
      8 years ago

      I was able to solve this problem using a paremeter in Power Query. The parameter is set to the users dropbox "home" local folder. Then, when I share the PBT file it prompts the user to enter their specific filepath before creating their user-specific pbix. This isn't quite automagical but it does the job. 

  • Liubov_Lehlai's avatar
    Liubov_Lehlai
    New Member
    2 years ago

    Do you know solution for Direct Query ? As i have Error "2 datasource is not allowed"