Power BI has become an essential tool for business intelligence and reporting.
However, managing Power BI reports across different environments or workspaces can be time-consuming and error-prone.
PowerShell offers an efficient and reliable method to automate the export and import of Power BI reports.
In this blog post, we’ll walk through how to use PowerShell to export Power BI reports from one workspace and import them into another. We’ll also cover how to update dataset parameters and refresh datasets after the import.
Prerequisites
- Power BI Management Module: Open PowerShell and run the following command to install the MicrosoftPowerBIMgmt module:
Install-Module -Name MicrosoftPowerBIMgmt - Power BI Account: You need access to the Power BI service with sufficient permissions to export and import reports.
- PowerShell: Ensure you’re using PowerShell 5.1 or later.
Step 1: Authenticate to Power BI Service
To interact with Power BI, you need to authenticate. There are multiple ways to authenticate:
- Interactive Login: Use this method if you want to log in interactively.
Connect-PowerBIServiceAccount
- User login: Use this method for non-interactive authentication.
$username = “” $password = “” | ConvertTo-SecureString -asPlainText -Force $credential = New-Object System.Management.Automation.PSCredential($username, $password) Connect-PowerBIServiceAccount -Credential $credential
- Service Principal: Use this method for non-interactive authentication.
$clientId = "your-client-id" $clientSecret = "your-client-secret" | ConvertTo-SecureString -AsPlainText -Force $tenantId = "your-tenant-id" $credential = New-Object System.Management.Automation.PSCredential($clientId, $clientSecret) Connect-PowerBIServiceAccount -ServicePrincipal -TenantId $tenantId -Credential $credential
Step 2: Export Power BI Reports
Once authenticated, you can export reports from a specific workspace. Here’s how:
1. Retrieve the Workspace:
$workspaceId = “your-workspace-id”
$workspace = Get-PowerBIWorkspace -Id $workspaceId
2. Export Reports:
Specify the export path and export the reports.
$exportPath = "C:\Temp\PowerBIExports"
if (!(Test-Path -Path $exportPath)) {
New-Item -ItemType Directory -Path $exportPath
}
$reports = Get-PowerBIReport -WorkspaceId $workspace.Id
foreach ($report in $reports) {
$filePath = Join-Path -Path $exportPath -ChildPath "$($report.Name).pbix"
Export-PowerBIReport -WorkspaceId $workspace.Id -ReportId $report.Id -OutFile $filePath
}
Step 3: Import Power BI Reports
After exporting the reports, you can import them into a new workspace.
1. Create a New Workspace:
$newWorkspace = New-PowerBIWorkspace -Name “New Workspace”
2. Import Reports: Import the reports from the export path.
$reportsToImport = Get-ChildItem -Path $exportPath -Filter "*.pbix"
foreach ($reportFile in $reportsToImport) {
$filePath = Join-Path -Path $exportPath -ChildPath $reportFile.Name
New-PowerBIReport -Path $filePath -Workspace $newWorkspace
}
Step 4: Update Dataset Parameters and Refresh
After importing the reports, you may need to update the dataset parameters and refresh the datasets.
1. Update Dataset Parameters:
$datasetId = "your-dataset-id"
$body = '{
"updateDetails": [
{
"name": "EnvironmentURL",
"newValue": "your-new-value"
}
]
}'
Invoke-PowerBIRestMethod -Url "/datasets/$datasetId/Default.UpdateParameters" -Method Post -Body $body
2. Refresh Dataset:
Invoke-PowerBIRestMethod -Method Post -Url “groups/$($newWorkspace.Id)/datasets/$datasetId/refreshes”
Step 5: Update Data Source Credentials You must update the credentials if the imported reports rely on data sources.
1. Retrieve Data Sources:
$datasources = Get-PowerBIDatasource -WorkspaceId $newWorkspace.Id -DatasetId $datasetId
2. Update Credentials:
foreach ($datasource in $datasources) {
$body = @{
"credentialDetails" = @{
"credentialType" = "OAuth2"
"encryptedConnection" = "Encrypted"
"useCallerAADIdentity" = $true
}
}
$jsonBody = $body | ConvertTo-Json -Depth 5
Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/gateways/$($datasource.GatewayId)/datasources/$($datasource.DatasourceId)" -Method PATCH -Body $jsonBody
}
Conclusion
By automating the export and import of Power BI reports using PowerShell, you can save time and reduce the risk of errors. This approach is particularly useful when managing multiple workspaces or environments. Whether you’re migrating reports or setting up new environments, PowerShell provides a flexible and powerful way to streamline the process.