Power BI Goals Pro Tip: Take over goal connections in a scorecard

[s-richert]

Run this script to bulk update goal connections in a given scorecard to use your own identity during query refresh. This is especially useful when a user is no longer present in the directory or somehow loses access to source datasets, leaving a lot of stale goal connections behind. We're working on bringing the "take over" functionality directly to the goals UI, but you might find this script handy in the meanwhile.

Important: This tip is meant for advanced users, only run the script if you understand its implications

How to use
1) Save the following script as a PowerShell file, for example scorecard-take-over-connections.ps1:

$api = "api.powerbi.com"
$token = ""
$scorecardId = ""
$newOwnerEmail = ""

while (-not $token) {
    $token = Read-Host -Prompt "Enter Power BI Access token"
}
while (-not $scorecardId) {
    $scorecardId = Read-Host -Prompt "Enter scorecard id"
}
$newOwnerEmail = Read-Host -Prompt "New goal owner email (leave empty to skip)"

$response = Invoke-WebRequest -Uri "https://$api/v1.0/myOrg/internalScorecards($scorecardId)?`$expand=goals" -Headers @{ "Authorization"="Bearer $token" }
$scorecard = $response.Content | ConvertFrom-Json

$connections = $scorecard.goals | Select id, name, valueConnection, targetConnection

Write-Host Number of goal value connections to update: $connections.Count

function TakeOverValueQuery($connection) {
 Write-Host " - Updating value connection: " -NoNewline
 $response = Invoke-WebRequest `
            -Method Post `
            -Uri "https://$api/v1.0/myOrg/internalScorecards($scorecardId)/goals($($connection.id))/UpsertGoalCurrentValueConnection()" `
            -Body ($connection.valueConnection | select datasetId, reportUrl, query | ConvertTo-Json) `
            -ContentType "application/json" `
            -Headers @{ "Authorization"="Bearer $token" }
 Write-Host -ForegroundColor green OK
}

function TakeOverTargetQuery($connection) {
 Write-Host " - Updating target connection: " -NoNewline
 $response = Invoke-WebRequest `
            -Method Post `
            -Uri "https://$api/v1.0/myOrg/internalScorecards($scorecardId)/goals($($connection.id))/UpsertGoalTargetValueConnection()" `
            -Body ($connection.targetConnection | select datasetId, reportUrl, query | ConvertTo-Json) `
            -ContentType "application/json" `
            -Headers @{ "Authorization"="Bearer $token" }
 Write-Host -ForegroundColor green OK
}

function UpdateGoalOwner($id, $newOwner) {
 Write-Host " - Updating owner: " -NoNewline
 $response = Invoke-WebRequest `
            -Method Patch `
            -Uri "https://$api/v1.0/myOrg/internalScorecards($scorecardId)/goals($($connection.id))" `
            -Body (@{ owner=$newOwner } | ConvertTo-Json) `
            -ContentType "application/json" `
            -Headers @{ "Authorization"="Bearer $token" }
 Write-Host -ForegroundColor green OK
}

$confirm = ""
foreach ($connection in $connections) {
    if ($connection.valueConnection -or $connection.targetConnection) {
        Write-Host -NoNewLine "Updating goal ""$($connection.name)"" ($($connection.id)) "
        if ($confirm -ne "a") {
            $confirm = Read-Host -Prompt "[Y]es/[N]o/[A]ll"
            if ($confirm -ne  "y" -and $confirm -ne "a") {
                continue;
            }
        } else {
            Write-Host
        }
        
        if ($connection.valueConnection) {
            TakeOverValueQuery -connection $connection
        }
        if ($connection.targetConnection) {
            TakeOverTargetQuery -connection $connection
        }
        if ($newOwnerEmail) {
            UpdateGoalOwner -id $connection.id -newOwner $newOwnerEmail
        }
    }    
}

2) Open a PowerShell prompt, and run the saved script. Then :

 a) Enter a Power BI access token (if you're not sure how to get one, check out the section below)
 b) Enter the id of the scorecard to take over

 c) If you also want to update the goal owner, type in the email for the new owner

 d) For each goal, enter "y" to confirm the update, or "n" to skip. Enter "a" to update all goal connections without confirmation.

How to get a Power BI access token

1. Open an admin PowerShell prompt and install the MicrosoftPowerBIMgmt module
2. Login to Power BI using Login-PowerBI cmdlet
3. Get the access token using the Get-PowerBIAccessToken cmdlet, and copy it (without the Bearer prefix)