Category: Aether

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."

As part of my work on Prism and other projects, managing Power BI refresh schedules at scale across multiple Power BI workspaces became essential. Manually setting schedules wasn’t scalable, and as Prism grew into a leading solution, automation became a necessity.

To solve this, I built a PowerShell script using the Power BI API. The script scans workspaces based on a defined description, applies a refresh schedule, and updates the dataset ID accordingly.

This script has now been used many times saving hours of manual work and hopefully it can help you too!

Why Automate?

Managing refresh schedules across multiple Power BI workspaces can quickly become time-consuming and inconsistent. Automating this process allows for simple bulk management and consistency.

How it works?

The script:

• Connects to the PowerBI service
• Retrieves all workspaces or filters with a specific name
• Searches for reports starting with “Aether” (modify as you need)
• Updates the refresh schedule to run daily at 16:00 UTC (modify time as you need)

https://github.com/AetherAdv/powerbi_powershell_updaterefresh

# Connect to Power BI Service Account

Connect-PowerBIServiceAccount

 

# Retrieve all workspaces

$workspaces = Get-PowerBIWorkspace -All

 

 

# Retrieve target workspace

#$workspaces = Get-PowerBIWorkspace -id "####"

 

# Loop through each workspace

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*' }

 

    # Check if any matching reports exist

    if ($Reportlist) {

        foreach ($Report in $Reportlist) {

 

            # Ensure the report has an associated DatasetId (required for refresh scheduling)

            if ($Report.DatasetId) {

                $ReportName = $Report.Name

                $WorkspaceId = $workspace.Id

                $ReportId = $Report.Id

                $DatasetId = $Report.DatasetId

 

                Write-Host "Updating refresh schedule for: $ReportName in Workspace: $WorkspaceId"

 

                # Construct the API URL for updating the refresh schedule

                $ApiUrl = "https://api.powerbi.com/v1.0/myorg/groups/$WorkspaceId/datasets/$DatasetId/refreshSchedule"

 

                # Define the refresh schedule settings

                $ApiRequestBody = @{

                    value = @{

                        days            = @("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")  # Runs daily

                        times           = @("16:00")  # Refresh time in UTC

                        notifyOption    = "MailOnFailure"  # Notify only on failure

                        localTimeZoneId = "UTC"  # Time zone for the refresh schedule

                        enabled         = $true  # Ensure the schedule is enabled

                    }

                } | ConvertTo-Json -Depth 3  # Convert to JSON format

 

                try {

                    # Send API request to update the refresh schedule

                    Invoke-PowerBIRestMethod -Url $ApiUrl -Method Patch -Body $ApiRequestBody -Verbose

                    Write-Host "Successfully updated data refresh schedule for: $ReportName" -ForegroundColor Green

                } catch {

                    # Handle any errors during the API request

                    Write-Host "Failed to update refresh schedule for: $ReportName" -ForegroundColor Red

                    Write-Host "Error: $_" -ForegroundColor Yellow

                }

            } else {

                # Skip reports without a DatasetId (they cannot have a refresh schedule)

                Write-Host "Skipping report '$ReportName' as it has no associated DatasetId." -ForegroundColor Yellow

            }

        }

    }

}