Power BI Goals Pro Tip: Copy scorecard script

[s-richert]

If you have a set of goals that you want to rollover into a new scorecard, or just want to bulk copy goals across scorecards, this utility script can save you some time by automating the copy process.

What gets copied:
   - Scorecard name, contact, and description (when duplicating scorecards)
   - Goal definitions (name, owners, dates, status rules). The relationship between parent/child goals is preserved as well.

What is not copied:
   - Scorecard settings like column configuration and custom statuses
   - Goal level permissions
   - Goal queries to source report
   - Goal values history and notes

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

Usage:
    - Copy the script to a new PowerShell file and save it. For example, "copy-scorecard.ps1"
    - Open a new PowerShell window, and run the script
       Note: the script will install MicrosoftPowerBIMgmt module if not present on the machine

    - Follow on screen instructions to duplicate a scorecard or copy goals 

 

 

$ErrorActionPreference = "Stop"

$publicEndpoint = "https://api.powerbi.com"

if (!(Get-Module -ListAvailable -Name MicrosoftPowerBIMgmt)) {
    try {
        Install-Module -Name MicrosoftPowerBIMgmt -AllowClobber -Confirm:$False -Force  
    }
    catch [Exception] {
        $_.message 
        exit
    }
}

Login-PowerBI

$token = (Get-PowerBIAccessToken)["Authorization"]

function GetApiUrl() {
    $response = Invoke-WebRequest -Uri "$publicEndpoint/metadata/cluster" -Headers @{ "Authorization"=$token }
    $cluster = $response.Content | ConvertFrom-Json
    return $cluster.backendUrl
}

$api = GetApiUrl

function GetScorecard($scorecardId) {
    Write-Host "Retrieving scorecard..." -NoNewLine
    $response = Invoke-WebRequest `
        -Uri "$api/v1.0/myOrg/internalScorecards($scorecardId)?`$expand=goals" `
        -Headers @{ "Authorization"=$token }
    Write-Host -ForegroundColor Green OK
    $scorecard = $response.Content | ConvertFrom-Json
    return $scorecard
}

function CopyGoal($sourceGoal, $destinationScorecardId, $parentGoalId) {
    Write-Host "Copying ""$($sourceGoal.name)""... " -NoNewline
    try {
        $newGoalRequest = $sourceGoal | select -Property name, startDate, completionDate, unit, owner, additionalOwners, valuesFormatString, datesFormatString
        if ($parentGoalId) {
            Add-Member -InputObject $newGoalRequest -Name parentId -MemberType NoteProperty -Value $parentGoalId
        }

        $response = Invoke-WebRequest `
            -Method Post `
            -Uri "$api/v1.0/myOrg/internalScorecards($destinationScorecardId)/goals" `
            -Headers @{ "Authorization"=$token } `
            -Body ($newGoalRequest | ConvertTo-Json) `
             -ContentType "application/json"
        
        $goal = $response.Content | ConvertFrom-Json

        Write-Host -ForegroundColor Green OK

        if ($sourceGoal.statusRules) {
            CopyStatusRules -goal $sourceGoal -destinationScorecardId $destinationScorecardId -destinationGoalId $goal.id
        }

        return $goal
    } catch {
        Write-host -ForegroundColor Red "Could not copy goal"
        throw
    }
}

function CopyStatusRules($goal, $destinationScorecardId, $destinationGoalId) {
    Write-Host " - Copying status rules... " -NoNewline
    $response = Invoke-WebRequest `
               -Uri "$api/v1.0/myOrg/internalScorecards($($goal.scorecardId))/goals($($goal.id))/statusRules" `
               -Headers @{ "Authorization"=$token }
    $rules = $response.Content | ConvertFrom-Json 
    $response = Invoke-WebRequest `
               -Method Post `
               -Uri "$api/v1.0/myOrg/internalScorecards($destinationScorecardId)/goals($destinationGoalId)/statusRules" `
               -Body ($rules | ConvertTo-Json -Depth 100) `
               -ContentType "application/json" `
               -Headers @{ "Authorization"=$token }
    Write-Host -ForegroundColor Green OK
}

function RecursiveCopy($sourceGoal, $goalsByParentId, $destinationScorecardId, $newParentId) {
    $goal = CopyGoal -sourceGoal $sourceGoal -destinationScorecardId $destinationScorecardId -parentGoalId $newParentId

    if ($goalsByParentId[$sourceGoal.id]) {
        foreach ($childGoal in $goalsByParentId[$sourceGoal.id]) {
            RecursiveCopy -sourceGoal $childGoal -goalsByParentId $goalsByParentId -destinationScorecardId $destinationScorecardId -newParentId $goal.id
        }
    }
}

function CopyAllGoals($sourceScorecard, $destinationScorecardId) {
    $goalsByParentId = @{}
    $topLevelGoals = @()
    foreach ($goal in $sourceScorecard.goals) {
        if ($goal.parentId) {
            if (!$goalsByParentId[$goal.parentId]) {
                $goalsByParentId[$goal.parentId] = @()
            }
            $goalsByParentId[$goal.parentId] += $goal
        } else {
            $topLevelGoals += $goal
        }
    }

    Write-Host "Copying $($sourceScorecard.goals.Length) goals to scorecard $destinationScorecardId..."

    foreach ($goal in $topLevelGoals) {
        RecursiveCopy -sourceGoal $goal -goalsByParentId $goalsByParentId -destinationScorecardId $destinationScorecardId -newParentId $null
    }

    Write-Host "Done"
}

function DuplicateScorecard($sourceScorecard, $destinationWorkspaceId, $newScorecardName) {
    Write-Host "Creating scorecard in workspace $destinationWorkspaceId..."
    
    $newScorecard = $sourceScorecard | select name, groupId, description, contact
    $newScorecard.groupId = $destinationWorkspaceId
    if ($newScorecardName) {
        $newScorecard.name = $newScorecardName
    }

    $response = Invoke-WebRequest `
            -Method Post `
            -Uri "$api/v1.0/myOrg/internalScorecards" `
            -Headers @{ "Authorization"=$token } `
            -Body ($newScorecard | ConvertTo-Json) `
            -ContentType "application/json"

    $destinationScorecard = $response.Content | ConvertFrom-Json

    Write-Host -ForegroundColor Green "Created scorecard $($destinationScorecard.id)"

    CopyAllGoals -sourceScorecard $sourceScorecard -destinationScorecardId $destinationScorecard.id
}

function ShowHelp() {
    Write-Host -ForegroundColor Yellow "This script can help you clone all goal definitions from an existing scorecard to a new or existing one"
    Write-Host -ForegroundColor Yellow "Before running the script, please make sure you have contributor permissions on the source scorecard and destination workspace"
    Write-Host -ForegroundColor Yellow "The following properties will not be copied:"
    Write-Host -ForegroundColor Yellow "  - Scorecard column settings, custom statuses"
    Write-Host -ForegroundColor Yellow "  - Goal permissions"
    Write-Host -ForegroundColor Yellow "  - Goal connections to  source reports"
    Write-Host -ForegroundColor Yellow "  - Goal values history"
    Write-Host -ForegroundColor Yellow "  - Goal notes"
}

function ShowPrompt() {
    while ($true) {
        Write-Host -ForegroundColor Yellow "Scorecard cloning utility"
        Write-Host "Choose action:"
        Write-Host "  Copy goals to a [n]ew scorecard (default)"
        Write-Host "  Copy goals to an [e]xisting scorecard"
        Write-Host "  [H]elp"
        Write-Host "  [Q]uit"

        $action = Read-Host -Prompt "Choose action or press enter to duplicate a scorecard"
        if ($action -eq "h") {
            ShowHelp
        } else {
            break
        }
    }

    if ($action -and  ($action -ne "n" -and $action -ne "e")) {
        if ($action -ne "q") {
            Write-Host -ForegroundColor red "Invalid action"
        }
        return
    }

    $scorecardId = Read-Host -Prompt "Enter source scorecard id"
    if (!$scorecardId) {
        Write-Error "Invalid scorecard id"
    }

    $sourceScorecard = GetScorecard -scorecardId $scorecardId
    Write-Host -ForegroundColor Green "Scorecard: $($sourceScorecard.name). Workspace: $($sourceScorecard.groupId)"


    if ($action -eq "n" -or !$action) {
        $newScorecardName = Read-Host -Prompt "Type new scorecard name (leave blank to reuse existing scorecard name)"
        $destinationWorkspaceId = Read-Host -Prompt "Enter destination workspace id (leave blank for 'My workspace')"
        if (!$destinationWorkspaceId) {
            $destinationWorkspaceId = "me"
        }

        DuplicateScorecard -sourceScorecard $sourceScorecard -destinationWorkspaceId $destinationWorkspaceId -newScorecardName $newScorecardName
    } elseif ($action -eq "e") {
        $destinationScorecardId = Read-Host -Prompt "Enter destination scorecard id"
        if ($destinationScorecardId) {
           if ($destinationScorecardId -ne $sourceScorecard.id) {
               CopyAllGoals -sourceScorecard $sourceScorecard -destinationScorecardId $destinationScorecardId
           } else {
               Write-Host -ForegroundColor Red "Source and destination scorecard must be different"
           }
        } else {
            Write-Host -ForegroundColor Red "Not a valid scorecard id"
        }
    }
}

ShowPrompt