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

    – 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] {


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

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") {
        } else {

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

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






Source link

Be the first to comment

Leave a Reply

Your email address will not be published.