REST API

PowerShell script to publish Power BI reports into multiple tenants

Andrzej Leszkiewicz

I’m working with multiple Power BI tenants, workspaces and reports. It’s a pain to log out (I’m always logged in with an account for previous report), copy a password from where it’s stored, paste the password, log in, click ‘Publish’ button, select workspace (make sure it’s correct tenant and correct workspace), confirm report replacing, then (optionally, e.g. to switch from stage to production database) open Power BI Service, log out (already did this in Power BI desktop, but need to do the same in Power BI Service), log in, open a workspace, open dataset settings, change parameters (e.g. database IP address), refresh the dataset.

So, I wrote a PowerShell script (based on Power BI Cmdlets for Windows PowerShell). The script uses a simple CSV file to determine what .pbix file goes into which tenant/workspace and what parameters to update (if any). First time it will ask for a password, encrypt and save it. Next time it’s a fully automated process. I just need to select a .pbix file in Total Commander, click Ctrl + Alt + F10 and the report will be published into a designated workspace (no chance to accidentally post it into a wrong tenant/workspace). If required, parameters will be changed and the dataset will be refreshed.

I’ll probably automate settings management, but it’s not critical. I need to add only one row (username, workspace name, report name, and parameters names/values (optionally) for a new report and then I can use the script with this report.

The script is really simple, based on the following algorithm (I’ve added links to the documentation directly in code samples):

1) verify if report name (full path has been received as a command line parameter) exists in the publisher.csv file. If exists then extract all values from a corresponding row (username, workspace name, dataset refresh request, parameter name and parameter value) from the publisher.csv file.

2) check if there is username.txt file in \data subfolder. If yes, then use username and password from this file. If not, then ask for a username and password, and save them into username.txt file (with encrypted password).

3) login into Power BI Service using previously saved username/password

#log in using PSCredential object
Connect-PowerBIServiceAccount -Credential $credential

4) find a workspace (by workspace name)

#get workspace by workspace name
$workspaceObject = ( Get-PowerBIWorkspace -Name $workspaceName )
#get #workspace id
$groupid=$workspaceObject.id

5) publish the report into a designated workspace

#publish a copy of the report (overwrite existing)
$result = New-PowerBIReport -Path $pbixPath -Name $reportName -Workspace $workspaceObject -ConflictAction CreateOrOverwrite

And get an id of the published/replaced dataset (Get-PowerBIDataset doesn’t work as expected with -Name parameter, workaround required – at least it didn’t work in the past and I’m using the workaround):

#get dataset 
#$dataset = Get-PowerBIDataset -Workspace $workspace -Name $reportName
#bug:
# https://github.com/microsoft/powerbi-powershell/issues/132
#workaround:
$dataset = Get-PowerBIDataset -Workspace $workspaceObject | Where-Object {$_.Name -eq $reportName}
#get dataset
$datasetid = $dataset.id

6) if required – update parameter (I’ll probably update this part in the future, to make it working with more than one parameter) using REST API

    #url and body for UpdateParameters API call
    $url = $urlbase + "UpdateParameters"
  
$body = @"
  {
  "updateDetails": [
    {
      "name": "$parameter1Name",
      "newValue": "$parameter1Value"
    }
  ]
  }
"@
  
    #update parameter's value
    Invoke-PowerBIRestMethod -Url $url -Method Post -Body $body

7) if required – refresh the report using REST API

#url and body for refreshes API call
  $url=$urlbase + "refreshes"
  $body = @"
  {
    "notifyOption": "NoNotification"
  }
"@
  
# Refresh the dataset
Invoke-PowerBIRestMethod -Url $url -Method Post -Body $body

8) log out

#Log out of the Power BI service
Disconnect-PowerBIServiceAccount

This is the entire script. As is. I see what can be optimized: better error handling, automated management of the publisher CSV file (to add new reports to the list by the script), ability to update more than one parameter and so on), but I have no need and time to do that right now. The script will be very useful for me as is, but I accept no responsibility for the results or lack of the results on your side. Make sure you understand what you’re doing before using the script (to do not publish a report into a wrong workspace and to do not replace a report with a wrong file). Feel free to ask any questions.

# Copyright: Andrzej Leszkiewicz, 2021
<#
.Synopsis
    Publishes selected Power BI report (.pbix file) into a designated workspace

.Description
    Publishes selected Power BI report (.pbix file) into a designated workspace.
    The script uses publisher.csv file to determine what report goes to which tenant/workspace.
    First time (for a user) the script will ask for a username and password. Next time no questions will be asked.

.Parameter pbixPath
    .pbix file to publish (full path)

.Example
    ./publsiher.ps1 "C:\powerbi\Report.pbix"

#>

# Microsoft's documentations ===================================================

#Microsoft Power BI Cmdlets for Windows PowerShell and PowerShell Core documentation
# https://docs.microsoft.com/en-us/powershell/power-bi/overview?view=powerbi-ps

# https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.profile/connect-powerbiserviceaccount?view=powerbi-ps
# https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.workspaces/get-powerbiworkspace?view=powerbi-ps
# https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.data/get-powerbidataset?view=powerbi-ps
# https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.reports/new-powerbireport?view=powerbi-ps
# https://docs.microsoft.com/en-us/powershell/module/microsoftpowerbimgmt.profile/invoke-powerbirestmethod?view=powerbi-ps

#Power BI REST APIs documentation
# https://docs.microsoft.com/en-us/rest/api/power-bi/

# ==============================================================================

# Command line Parameters ======================================================
[CmdletBinding()]
param
(
    
    [string] $pbixPath

)

# ==============================================================================

#TLS1.2
#[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

#Install Microsoft Power BI Cmdlets for Windows PowerShell
#Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser

#get report file name from the full path
$report = Get-ChildItem  $pbixPath
$reportFileToPublish = $report.name

Write-Host "`nReport file to publish: "$reportFileToPublish

#read data from the .csv file
$reports = import-csv $PSScriptRoot\publisher.csv

#for all records (reports) in the CSV file
Foreach ($i in $reports) {

    #get report name
    $reportName = $i.report
    $reportFileName = $i.report + ".pbix"

    if ($reportFileToPublish -eq $reportFileName) {
    #report found in the CSV file

        $workspaceName = $i.workspace
        $userName = $i.username
        $parameter1Name = $i.Param1Name
        $parameter1Value = $i.Param1Value
        #$parameter2Name = $i.Param2Name
        #$parameter2Value = $i.Param2Value
        $datasetRefresh = $i.datasetRefresh

        break

    }

}

if (!($userName)) {
Write-Host "`nReport not found in the CSV file!`n"
exit
}
else {
Write-Host "`nUserName: "$userName
}

$credentialsFile = "$PSScriptRoot\data\$userName.txt"

#read username and encrypted password from a file
if (Test-Path $PSScriptRoot\data\$userName.txt) { 

    $azure_username = Get-Content $PSScriptRoot\data\$userName.txt -First 1
    $securepassword = (Get-Content $PSScriptRoot\data\$userName.txt -TotalCount 2)[-1] | ConvertTo-SecureString

    Write-Host "Stored credentials found"

}
else { 

    Write-Host "Stored credentials not found! In the pop up window enter you Azure (Power BI Service) username and password:"
    
    $credential = Get-Credential 
    $azure_username = $credential.UserName
    $securepassword = $credential.Password

    $output = $azure_username + "`r`n" + ($securepassword| ConvertFrom-SecureString)
    $output | Set-Content $PSScriptRoot\data\$userName.txt

    Write-Host "Credentials saved"

}

Write-Host "`nSigning in to a Microsoft Azure account:"

#get PSCredential object 
$credential = New-Object System.Management.Automation.PSCredential ( $azure_username, $securepassword)
#log in using PSCredential object
Connect-PowerBIServiceAccount -Credential $credential

#get workspace by workspace name
$workspaceObject = ( Get-PowerBIWorkspace -Name $workspaceName )
#get #workspace id
$groupid=$workspaceObject.id

#publish a copy of the report (overwrite existing)
$result = New-PowerBIReport -Path $pbixPath -Name $reportName -Workspace $workspaceObject -ConflictAction CreateOrOverwrite
#get report id
$reportid = $result.id

Write-Host "============================================================================`n"
Write-Host -NoNewline "Report: "$reportName" (published)`n`nReport id: "$reportid

Write-Host "`n`nWorkspace id: "$workspaceObject.id

#get dataset 
#$dataset = Get-PowerBIDataset -Workspace $workspace -Name $reportName
#bug:
# https://github.com/microsoft/powerbi-powershell/issues/132
#workaround:
$dataset = Get-PowerBIDataset -Workspace $workspaceObject | Where-Object {$_.Name -eq $reportName}
#get dataset
$datasetid = $dataset.id

Write-Host "`nDatasetid: "$dataset.id

$urlbase = "groups/$groupid/datasets/$datasetid/"

#update parameters

if ($parameter1Name) { 

    #url and body for UpdateParameters API call
    $url = $urlbase + "UpdateParameters"
  
$body = @"
  {
  "updateDetails": [
    {
      "name": "$parameter1Name",
      "newValue": "$parameter1Value"
    }
  ]
  }
"@
  
    #update parameter's value
    Invoke-PowerBIRestMethod -Url $url -Method Post -Body $body
  
    Write-Host "`n -> parameter '"$parameter1Name"' updated. New value: "$parameter1Value
}
else {
    Write-Host "`nNo parameters to update"
}

#dataset refresh

if ($datasetRefresh -eq "yes") { 

#url and body for refreshes API call
  $url=$urlbase + "refreshes"
  $body = @"
  {
    "notifyOption": "NoNotification"
  }
"@
  
# Refresh the dataset
Invoke-PowerBIRestMethod -Url $url -Method Post -Body $body

Write-Host " -> dataset refreshed"

}
else {
    Write-Host "`nDataset refresh not required"
}

#open workspace in a browser
#$workspaceURL = "https://app.powerbi.com/groups/$groupid/list/dashboards"
#start $workspaceURL

#Log out of the Power BI service
Disconnect-PowerBIServiceAccount

Write-Host "`nLogged out"

# ==============================================================================

I’m attaching .ps1 file (rename the file into publisher.ps1 after downloading):

publisher.ps1renameDownload

And you can create you own CSV file. Format of the publisher.csv file (required fields):

username,workspace,report,DatasetRefresh,param1Name,param1Value

You’ll need to add one row of data for each report.
username – email address
workspace – workspace name
report – report name
DatasetRefresh – “yes” if this dataset has to be refreshed after publsihing
param1Name – name of a parameter
param1Value – new value for <param1Name> parameter

I run a script by selecting a .pbix file in Total Commander and clicking Ctrl+Alt+F10 (this will run the scrip in powershell.exe with pbix file name as a parameter). But I want to be able to do the same using a button on the ‘External Tools’ tab in Power BI Desktop. Not possible for now (at least without very tricky workaround). Vote, to make it possible: https://ideas.powerbi.com/ideas/idea/?ideaid=632965ab-e260-eb11-8fed-281878bda47d

Share the article