cancel
Showing results for 
Search instead for 
Did you mean: 
s-richert

Power BI Goals Pro Tip: Copy scorecard script

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 

 

copy scorecard 2.gif

 

 

$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

 

 

 

 

Comments

Hi, this is awesome! Thanks so much for sharing. I tried the script to copy to a new workspace (not "me") and it did not work sadly - any ideas why? @s-richert 

@Kaileen_Silva , thank you for the feedback. What error do you see when attempting to copy the scorecard in a new workspace ? A couple things to check :
 - Do you have contributor/admin permissions in the destination workspace ? 
 - Is this a v1 workspace ? (scorecards are only supported in v2 workspaces)

 

Hey Guys,

 

This is a great script! 

I'm trying to adapt it to also recovery the notes that were made in the each Goal. I've seen that there is an object related to the notes but I'm not able to access it.

Do you have any piece of code that show how to do that??

 

Thanks a lot,

Best Regards,

Polls
What is your favorite Power BI feature release for June 2022?