cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JorgeDiego Regular Visitor
Regular Visitor

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, with the same schema. 

 

Currently, I develop a report using an internal data base and then I change this report data source for every customer and I upload the pbix file to them power bi account. So, what I want to do is to make this process programmatically to do it faster and avoid possible errors. 

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Change pbix data source programmatically

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:

 

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

15 REPLIES 15
Community Support Team
Community Support Team

Re: Change pbix data source programmatically

Hi @JorgeDiego,

 

You can take a look at below link which about use parameterized power query function to load the data source:

 

Deep Dive into Query Parameters and Power BI Templates

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
hugoberry Member
Member

Re: Change pbix data source programmatically

Using parameters as @v-shex-msft was mentioning plus think about storing a file with connection string as its contents and store it in the same location for each of the client. Then on opening the PBIT the parametrs can be prepopulated with the contents of this stored file.

 

Otherwise you can think about a more (Power Query M) scripted approach where you parse PBIX/PBIT files, extract the query scripts do the replacements and figure out how to re-save that as a new PBIX/PBIT file.

JorgeDiego Regular Visitor
Regular Visitor

Re: Change pbix data source programmatically

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
Community Support Team
Community Support Team

Re: Change pbix data source programmatically

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:

 

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

JorgeDiego Regular Visitor
Regular Visitor

Re: Change pbix data source programmatically

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 Frequent Visitor
Frequent Visitor

Re: Change pbix data source programmatically

Could you please elaborate the last part

Source= //invoke data connection method here

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

Kenkamran Frequent Visitor
Frequent Visitor

Re: Change pbix data source programmatically

Pbix is just a zip package. Can you open the package and change the connection file in it. All this can be done programmatically.

Kenkamran Frequent Visitor
Frequent Visitor

Re: Change pbix data source programmatically

Pbix is just a zip package. Can you open the package and change the connection file in it. All this can be done programmatically.

Kenkamran Frequent Visitor
Frequent Visitor

Re: Change pbix data source programmatically

public static class Misc{


public static void UpdateConnection(string filepath)
{
using (ZipArchive archive = new ZipArchive(File.Open(filepath, FileMode.Open), ZipArchiveMode.Update,false,null)){
ZipArchiveEntry entry = archive.GetEntry("Connections");
string newstring;
using (var sr = new StreamReader(entry.Open())){

var jsonText = sr.ReadToEnd();
newstring = UpdateServer("MyTestServer", jsonText);
}
using (var sw = new StreamWriter(entry.Open())){
sw.Write(newstring);
}
}
}


private string UpdateServer(string server,string jsonText){
var pattern = @"Data Source\s*=\s*\w+\b";
return Regex.Replace(jsonText, pattern, $"Data Source={server}");
}
}

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 46 members 809 guests
Please welcome our newest community members: