Did you know that you can update PowerBI parameters with PowerShell across all reports in seconds?
Managing Prism, I rely on parameters for version tracking as well as data sources, making things like infrastructure changes or performing updates much easier. However, managing this at scale meant manual changes was just going to take forever.
Following on from last week’s blog, this post shows how we can automate updating PowerBI parameters with PowerShell.
It’s so much faster!
For example, if you have set a data source via a parameter or another field that may be consistent across your PowerBI solutions. This script will allow you to modify those parameters without having to update each one manually in the PowerBI service.
This script has saved literal hours when deploying updates or infrastructure changes, allowing me to focus on what’s next rather than manually tweaking everything.
Why Automate?
Manually updating parameters across multiple PowerBI workspaces is very time consuming. Automation in this area ensures:
- Faster updates when modifying reports and workspaces
- Consistency of change across all reports
- Less manual effort, freeing up time for the team
How it works?
The script:
- Connects to the PowerBI Service
- Define the parameter we want to set (change this as required)
- Retrieves all PowerBI workspaces or choose to target those with specific IDs
- Searches for reports starting with “Aether” (change to what you need)
- Reads the parameters attached to a data set
- Updates the parameter in the specified position / name (modify this as you need)
Connect-PowerBIServiceAccount
# Updates first parameter - parameter 0
$UpdateParameter0 = "MyNewParameterValue"
$workspaces = Get-PowerBIWorkspace -All
foreach ($workspace in $workspaces) {
# Get reports with names starting with "AETHER" in the current workspace
$Reportlist = Get-PowerBIReport -WorkspaceId $workspace.Id | Where-Object { $_.Name -like 'AETHER*' }
if ($Reportlist) {
Write-Host "Workspace: $($workspace.Name)"
foreach ($Report in $Reportlist) {
Write-Host " Report: $($Report.Name)"
$JsonString = $null
$ParametersJsonString = Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.Id)/datasets/$($Report.DatasetId)/parameters" -Method Get
$Parameters = (ConvertFrom-Json $ParametersJsonString).value
$UpdateParameterList = @() # Initialize as an empty array
foreach ($Parameter in $Parameters) {
$UpdateParameterList += @{ "name" = $Parameter.name; "newValue" = $Parameter.currentValue }
}
if ($UpdateParameterList.Count -gt 0) { #check if parameters exist.
$currentparam = $UpdateParameterList[0].newValue
Write-Host " Current Parameter 0 Value: $currentparam"
if ($currentparam -ne $UpdateParameter0) {
Write-Host "Parameter 0 value does not match. Updating..."
$UpdateParameterList[0].newValue = $UpdateParameter0
}
else{
Write-Host "Parameter 0 value already matches. Skipping update."
}
$JsonBase = @{ "updateDetails" = $UpdateParameterList }
$JsonString = $JsonBase | ConvertTo-Json
# Update the parameters
Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.Id)/datasets/$($Report.DatasetId)/Default.UpdateParameters" -Method Post -Body $JsonString
Start-Sleep -Seconds 5
# Refresh the data
Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.Id)/datasets/$($Report.DatasetId)/refreshes" -Method Post
Write-Host "Refresh started."
}
else{
Write-Host "No parameters found for this dataset."
}
}
} else {
Write-Host "No reports found in workspace: $($workspace.Name)"
}
}
Write-Host "Script completed."