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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Also interested in this option; here's what I've found so far:

 

1. The pbit/pbix files are basic zip files, with the extension changed; you can unzip and rezip and PowerBI will recognize them

2. The DataMashup file within the archive is also an archive, but it is NOT a basic zip file; unzipping and rezipping will not be recognized by PowerBI... but this archive contains Section1.m which has the query source definitions (likely what we need to change programmatically to achieve Jorge's desired solution)

3. DataMashup seems to be a MS-QDEFF file: https://docs.microsoft.com/en-us/openspecs/office_file_formats/ms-qdeff/e6377721-700b-411c-9d4f-3fc02472454b

 

So at least the potential workflow for programmatically changing the source would be:

a. upzip the pbit/pbix archive

b. parse/unpack the DataMashup file, following the MS-QDEFF structure rules

c. change Section1.m

d. repackage DataMashup, following the MS-QDEFF structure rules

e. rezip the entire archive, including the new DataMashup and change the extension back to pbit or pbix

 

Python's zipfile library can do steps (a) & (e) easily, and basic python string manipulation can handle (c). Challenge is writing some functions to handle the MS-QDEFF parsing/writing... 

 

 

SRPSnowBird's avatar
SRPSnowBird
Frequent Visitor
3 years ago

We have a lot of data in our company. Currently, we rely on manually creating ad hoc Power BI reports for each customer. Ideally, we could use programming languages such as Java, .NET, Python, or R to handle the ETL work and then use code to generate Power BI reports by inserting built-in visuals and publishing them automatically. I found that the REST API can push datasets into the workspace. There are also Power BI development tools for creating customized visuals. However, for our use case, the typical built-in visuals are good enough. My question is: is there any way I can use a programming language to insert built-in visuals in Power BI?