REST API

PowerShell script to download Power BI report from Power BI Service

Andrzej Leszkiewicz

What if I need to download .pbix file from Power BI service? I’ll need it if a report has been edited in Power BI Service or if I just want to have a version after the most recent scheduled data refresh (without refreshing it in Power BI Desktop). I can open a browser, logout from Power BI Service (if I was logged in to a wrong tenant), login, open a workspace, find my report, download a .pbix file, backup my local version of the file and replace it with the file downloaded from Power BI Service.

Nothing complicated, but it takes more than just a keyboard shortcut, and I’m lazy (efficient). With my PowerShell script I’ll just select .pbix file that I already have on my computer then use a keyboard shortcut and cloud version of the report will be automatically exported from Power BI Service into .pbix file. Local (selected in Total Commander) version of the file will be automatically archived and copied into _BACKUP subfolder, and replaced with the downloaded version. I’m ready to edit it in Power BI Desktop.

More detailed description of how it works: the script receives .pbix file name, look ups if same report name exists in the ‘reports.csv’ file and extracts username (e-mail) and workspace name from the .csv file.

Note: It’s the same CSV file that I used for my other PowerShell script, but it has been renamed. The CSV file contains a list of reports with account names and workspace names.

Then the script checks if there is a password saved for this username. If it was already saved then the script will use this password, otherwise it will ask a user to enter username and password (the credentials will be saved (password encrypted), next time the script won’t ask for any input).

The scripts connects to Power BI Services, verifies if there is a workspace with the name extracted from the .csv file and a report with the same name as the local .pbix file. Then it downloads the report from Power BI Service and replaces local file with the cloud version of the report.

Before file replacing the script uses WinRar to create a backup copy of the local report file (.rar archive will be created in _BACKUP subfolder). And it creates one more (hidden) backup copy of the file (hidden .pbix.bak copy of the .pbix file will be created)

I assume you understand (or willing to learn) some basics of PowerShell scripting and I’ve added links to the documentation for all Power BI Cmdlets used in the script. I never used PowerShell before I started to work with Power BI, but it was easy for me to start (knowing some basics of other programming languages).

This is the entire script (+ see the attached file):

# Copyright: Andrzej Leszkiewicz, 2021
<#
.Synopsis 
    To replace selected .pbix file with it's cloud version
     
.Description
    Script to download cloud version of the selected .pbix file from Power BI Service
     and replace local version of the file with the file downloaded from Power BI Service.
     A copy of the selected .pbix file will be archived and saved into the _BACKUP subfolder before replacement.

.Parameter pbixFile
    .pbix file to replace with it's cloud version (file name)

.Example
    ./servicetodesktop.ps1 "ReportName.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

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

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

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

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

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

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

Write-Host "Report file to download from Power BI Service: $pbixFile"

#read data from the .csv file (with a list of reports I work with)
$reports = import-csv $PSScriptRoot\reports.csv

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

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

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

        $workspaceName = $i.workspace
        $userName = $i.username

        break

    }

}

if (!($userName)) {
  Write-Host
  Write-Host "Report not found in the CSV file!"
  Write-Host
  exit
}

Write-Host
Write-Host "Username: $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 

}
else { 

    Write-Host
    Write-Host "Stored credentials not found. User will be asked to enter username and password."
    Write-Host
    
    #ask user to enter username and password
    $credential = Get-Credential 
    $azure_username = $credential.UserName
    $securepassword = $credential.Password

    #encrypt password and save into a file
    $output = $azure_username + "`r`n" + ($securepassword| ConvertFrom-SecureString)
    $output | Set-Content $PSScriptRoot\data\$userName.txt

    Write-Host "Credentials saved"
    Write-Host

}

Write-Host
Write-Host "Signing 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
$Workspace = Get-PowerBIWorkspace -Name $workspaceName

if (!($Workspace)) {
Write-Host "Workspace $workspaceName not found"
exit
} 

$workspaceId = $Workspace.id

Write-Host "Workspace: $workspaceName $workspaceId"
Write-Host

#get the report

$report = Get-PowerBIReport -Name $reportName -Workspace $Workspace

if ($report) {

    #report Id
    $reportId = $report.id
    
    Write-Host "Report: $reportName $reportId"

    #current folder and full file name
    $folder = (Get-Location)
    $fileNameFull = "$folder\$pbixFile"
    $fileNameFullDest = "$folder\$pbixFile.bak" 
    
    #backup local .pbix file using WinRAR
    Start-Process "cmd.exe" "/C MKDIR _BACKUP"
    Start-Process  -FilePath "c:\Program Files\WinRAR\Rar.exe" -ArgumentList "a -agYYYY_MM_DD_{BAK-REPL-}NN _BACKUP\ $pbixFile" #-WindowStyle Maximized
    
    #make a copy of the .pbix file (double backup)
    Move-Item -Path $fileNameFull -Destination $fileNameFullDest -Force    
    #hide file copy
    Attrib +h $fileNameFullDest

    #download .pbix file from Power Bi Service 
    Export-PowerBIReport -Id $reportId -OutFile $fileNameFull

}
else {
    Write-Host "Report not found in the workspace"
}

#Log out of the Power BI service
Disconnect-PowerBIServiceAccount

A disclaimer, as usual: For sure the script can be improved (with better error handling etc.), but for now it works and it does what I need. 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 lose your reports, the script moves/removes/replaces files). Feel free to ask any questions.

ServiceToDesktopPS1Download
Share the article