We had a similar issue. We had a report connected to a dataset using a live-connection. We needed to repoint the live connection at a dataset in a different workspace (we needed to move it to a different Premium Capacity workspace). The main reason we needed to do this, is so that we wouldnt change the report URL that everyone was already using.
What we wanted:
Workspace A (Report A) --------> Workspace 1 (Dataset A)
After change:
Workspace A (Report A) --------> Workspace 2 (Dataset A')
Here is everything we tried:
1. Directly updating the dataset using the "Transform Data" >> "Datasource settings".
This seemed to work locally, but when we published the report back to its workspace, it magically created 2 reports with the exact same name. The original report remained untouched.
2. Rebinding using PowerShell
The code I used is at the bottom of this post. This worked! Except for one major issue. The report was repointed at the new dataset in the new workspace, but when we downloaded the PBIX file, it still was pointed at the dataset in the original workspace. This would not work, as we still need to continue to build the pages in this report.
3. Using PBIP file (this is the solution)
a. We downloaded the file from PowerBi.com and opened the file in PowerBi Desktop and saved it as a PBIP file.
b. Closed PowerBi desktop and in the folder where we saved the PBIP file, within the "xxxxx.Report" subfolder, we opened the "definintion.pbir" file and edited in NotePad. Specifically the "connection string" was updated:
Original pbir file:
{
"version": "4.0",
"datasetReference": {
"byConnection": {
"connectionString": "Data Source=\"powerbi://api.powerbi.com/v1.0/myorg/Workspace 1\";Initial Catalog=\"Dataset A\";Access Mode=readonly;Integrated Security=ClaimsToken",
"pbiServiceModelId": null,
"pbiModelVirtualServerName": "sobe_wowvirtualserver",
"pbiModelDatabaseName": "Dataset A's GUID",
"name": "EntityDataSource",
"connectionType": "pbiServiceXmlaStyleLive"
}
}
}
PBIR file after update:
{
"version": "4.0",
"datasetReference": {
"byConnection": {
"connectionString": "Data Source=\"powerbi://api.powerbi.com/v1.0/myorg/Workspace 2\";Initial Catalog=\"Dataset A'\";Access Mode=readonly;Integrated Security=ClaimsToken",
"pbiServiceModelId": null,
"pbiModelVirtualServerName": "sobe_wowvirtualserver",
"pbiModelDatabaseName": "Dataset (A')'s GUID",
"name": "EntityDataSource",
"connectionType": "pbiServiceXmlaStyleLive"
}
}
}
c. Opened the PBIP file in Desktop and saved the file as PBIX (this step is not really needed).
d. Published the report back to Workspace A. Voila, the original report was now repointed at Dataset A` in Workspace 2.
PowerShell script used to rebind report
#report details that needs to be rebound
$reportWorkspaceName = "Workspace 1"
$reportName = "Report 1"
#new dataset details that report needs to be bound to
$datasetWorkspaceName = "Workspace 2"
$datasetName = "Dataset A`"
function RebindReport{
param(
[string]$workspaceId,
[string]$reportId,
[string]$datasetIdToRebindTo
)
if ($workspaceId -eq $null -or $reportId -eq $null -or $datasetIdToRebindTo -eq $null)
{
throw "WorkspaceId, ReportId, and DatasetIdToRebindTo must be specified"
}
$ApiUrl = "https://api.powerbi.com/v1.0/myorg/groups/${workspaceId}/reports/${reportId}/Rebind"
$ApiRequestBody = "{'datasetId': '${datasetIdToRebindTo}'}"
$ApiRequestBody
$response = $null
try
{
Write-Host "Rebinding Report"
$response = Invoke-PowerBIRestMethod -Url $ApiUrl -Method Post -Body ($ApiRequestBody)
Write-Host "Rebind completed"
Write-Host "Complete"
}
catch
{
Write-Host "An error occurred:"
Write-Host $_
}
$response
}
$ErrorActionPreference = "Stop"
Login-PowerBI | Out-Null
#report info
$WorkspaceObject = (Get-PowerBIWorkspace -Name $reportWorkspaceName)
$PbiReportObject = (Get-PowerBIReport -Workspace $WorkspaceObject -Name $reportName)
$reportWorkspaceId = $WorkspaceObject.Id
$reportId = $PbiReportObject.Id
$oldDatasetForReport = $PbiReportObject.DatasetId
#dataset to rebind to info
Write-Host "Getting info for dataset ${datasetName} in ${datasetWorkspaceName}"
$datasetWorkspaceObject = (Get-PowerBIWorkspace -Name $datasetWorkspaceName)
#$PbiDatasetObject = (Get-PowerBIDataset -Workspace $datasetWorkspaceObject -Name $datasetName -Scope Individual)
$PbiDatasetObject = (Get-PowerBIDataset -Workspace $datasetWorkspaceObject) | Where-Object {$_.Name -eq $datasetName}
$PbiDatasetObject
$datasetWorkspaceId = $datasetWorkspaceObject.Id
$datasetId = $PbiDatasetObject.Id
Write-Host "Rebinding ReportId: ${reportId}"
Write-Host "in workspace ${reportWorkspaceId}"
Write-Host "from ${oldDatasetForReport}"
Write-Host "to ${datasetId} in ${datasetWorkspaceId}"
RebindReport -workspaceId $reportWorkspaceId -reportId $reportId -datasetIdToRebindTo $datasetId
$PbiReportObject = (Get-PowerBIReport -Workspace $WorkspaceObject -Name $reportName)
$PbiReportObject
