It depends on your data source type (SQL Server, SSAS, CSV files, etc.) and data connectivity mode (import, direct query, etc.). If you can use parameters, then one of your options is to allow the newly cloned report to switch it's data source itself by using connection specific parameters. To do this, open Power Query Editor
by clicking Edit Queries
and in Manage Parameters
define two new text parameters, lets name them ServerName
and DatabaseName
:
Set their current values to point to one of your data sources, e.g. SQLSERVER2016
and AdventureWorks2016
. Then right click your query in the report and open Advanced Editor
. Find the server name and database name in the M code:
and replace them with the parameters defined above, so the M code will look like this:
Now you can close and apply changes and your report should work as before. But now when you want to change the data source, do it using Edit Parameters
:
and change the server and/or database name to point to the other data source, that you want to use for your report:
After changing parameter values, Power BI Desktop will ask you to apply the changes and reload the data from the new data source. To change the parameter values (i.e. the data source) of a report published in Power BI Service, go to dataset's settings and enter new server and/or database name (check the gateway settings too, if this is on-premise data source):
After changing the data source, refresh your dataset to get the data from the new data source. With Power BI Pro account you can do this 8 times per 24 hours, while if the dataset is in a dedicated capacity, this limit is raised to 48 times per 24 hours.
To do this programatically, use Update Parameters
/ Update Parameters In Group
and Refresh Dataset
/ Refresh Dataset In Group
REST API calls. For example, you can do this with PowerShell like this:
Import-Module MicrosoftPowerBIMgmt
Import-Module MicrosoftPowerBIMgmt.Profile
$password = "xxxxx" | ConvertTo-SecureString -asPlainText -Force
$username = "[email protected]"
$credential = New-Object System.Management.Automation.PSCredential($username, $password)
Connect-PowerBIServiceAccount -Credential $credential
Invoke-PowerBIRestMethod -Url 'groups/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/datasets/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/UpdateParameters' -Method Post -Body '{
"updateDetails": [
{
"name": "ServerName",
"newValue": "SQLSERVER2019"
},
{
"name": "DatabaseName",
"newValue": "AdventureWorks2019"
}
]
}'
Invoke-PowerBIRestMethod -Url 'groups/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/datasets/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/refreshes' -Method Post
Disconnect-PowerBIServiceAccount
If you can't use parameters, e.g. Live connection to SSAS, the connection string could be changed using Update Datasources In Group REST API call. In PowerShell this could be done like this:
Import-Module MicrosoftPowerBIMgmt
Import-Module MicrosoftPowerBIMgmt.Profile
$password = "xxxxx" | ConvertTo-SecureString -asPlainText -Force
$username = "[email protected]"
$credential = New-Object System.Management.Automation.PSCredential($username, $password)
Connect-PowerBIServiceAccount -Credential $credential
Invoke-PowerBIRestMethod -Url 'groups/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/datasets/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/Default.UpdateDatasources' -Method Post -Body '{
"updateDetails": [
{
"datasourceSelector": {
"datasourceType": "AnalysisServices",
"connectionDetails": {
"server": "My-As-Server",
"database": "My-As-Database"
}
},
"connectionDetails": {
"server": "New-As-Server",
"database": "New-As-Database"
}
}
]
}'
Disconnect-PowerBIServiceAccount
Note, that you need to provide both old and new server and database names.
In C# you can do the same in a very similar way, even without Power BI Client:
var group_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";
var dataset_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";
var client = new HttpClient();
client.DefaultRequestHeaders.Add("Accept", "application/json");
client.DefaultRequestHeaders.Add("Authorization", "Bearer " + accessToken);
var restUrlUpdateParameters = $"https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets/{dataset_id}/Default.UpdateParameters";
var postData = new { updateDetails = new[] { new { name = "ServerName", newValue = "NEWSERVER" }, new { name = "DatabaseName", newValue = "Another_AdventureWorks2016" } } };
var responseUpdate = client.PostAsync(restUrlUpdateParameters, new StringContent(JsonConvert.SerializeObject(postData), Encoding.UTF8, "application/json")).Result;
var restUrlRefreshDataset = $"https://api.powerbi.com/v1.0/myorg/groups/{group_id}/datasets/{dataset_id}/refreshes";
var responseRefresh = client.PostAsync(restUrlRefreshDataset, null).Result;
Using the Power BI C# client can make you life easier, e.g. refreshing the report can be made this way:
var group_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";
var dataset_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";
var credentials = new TokenCredentials(accessToken, "Bearer");
using (var client = new PowerBIClient(new Uri("https://api.powerbi.com"), credentials))
{
client.Datasets.RefreshDatasetInGroup(group_id, dataset_id);
}
When calling the API, you need to provide an access token. To acquire it use ADAL or MSAL libraries, e.g. with code like this:
private static string resourceUri = "https://analysis.windows.net/powerbi/api";
private static string authorityUri = "https://login.windows.net/common/"; // It was https://login.windows.net/common/oauth2/authorize in prior versions
private static string clientId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"; // Register at https://dev.powerbi.com/apps
private static string groupId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";
private static string reportId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx";
private static AuthenticationContext authContext = new AuthenticationContext(authorityUri, new TokenCache());
public string Authenticate()
{
AuthenticationResult authenticationResult = null;
// First check is there token in the cache
try
{
authenticationResult = authContext.AcquireTokenSilentAsync(resourceUri, clientId).Result;
}
catch (AggregateException ex)
{
AdalException ex2 = ex.InnerException as AdalException;
if ((ex2 == null) || (ex2 != null && ex2.ErrorCode != "failed_to_acquire_token_silently"))
{
MessageBox.Show(ex.Message);
return;
}
}
if (authenticationResult == null)
{
var uc = new UserPasswordCredential("[email protected], "Strong password");
try
{
authenticationResult = authContext.AcquireTokenAsync(resourceUri, clientId, uc).Result;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + ex.InnerException == null ? "" : Environment.NewLine + ex.InnerException.Message);
return;
}
}
if (authenticationResult == null)
MessageBox.Show("Call failed.");
else
{
return authenticationResult.AccessToken;
}
}