Forum Discussion

JorgeDiego's avatar
JorgeDiego
Advocate 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

Kenkamran's avatar
Kenkamran
Regular Visitor
8 years ago

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}");
}
}

kkalyanthaya's avatar
kkalyanthaya
Microsoft Employee
8 years ago

Did this work ? I get an error message saying corrupted file or file created from unsupported version of Poewr BI.