How to Execute Power BI Report Using Power BI REST API in PowerShell

Whether for deployment pipelines or embedded Power BI reporting, having a simple way to test and work with reports through the API is an important requirement for the Power BI developer. The simplest way to accomplish this is using PowerShell. You will first want to go out and download Visual Studio code and install the newest version of PowerShell (7.2 at the time of writing this), you need the newest version of PowerShell to run these scripts. Below is the full PowerShell script to run your Power BI reports from the API.

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Authenticate with the Power BI service

Connect-PowerBIServiceAccount

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Get an access token. Only needs to be run once and will expire in an hour

$headers = Get-PowerBIAccessToken

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Set the target workspace, report, and export file target

$workspacename = "Your Workspace Name"
$reportname = "Your Report Name"
$exportfile = "C:\Dump\test.pdf"

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<# Set the body of the api call. In the example below we have specified the export format "PDF"
and the target report parameters and values. The example below is targeting the paginated report
configuration but you can also target the power bi report configuration for regular reports.
You can configure many options in the body depending on the report configuration target.

More information can be found here:

https://docs.microsoft.com/en-us/power-bi/developer/embedded/export-paginated-report
https://docs.microsoft.com/en-us/rest/api/power-bi/reports/export-to-file
#>


$body = @"
{
"format": "PDF",
"paginatedReportConfiguration":{
"parameterValues":[
{"name": "OrderDate", "value": "7/11/2022"},
{"name": "OrderNumber", "value": "267563"}
]
}
}
"@

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Retrieve the target workspace id

$uri = "https://api.powerbi.com/v1.0/myorg/groups" # In Power BI, "groups" refers to the workspaces, as in workspace group.
$invkrest = Invoke-RestMethod -Uri $uri -Method GET -Headers $headers
$workspace = $invkrest.value | Where-Object{$_.name -eq $workspacename}

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Retrieve the target report id in the target workspace id.

$uri = "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.Id)/reports"
$invkrest = Invoke-RestMethod -Uri $uri -Method GET -Headers $headers
$report = $invkrest.value | Where-Object{$_.name -eq $reportname}

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Kick off the report export process

$url = "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.id)/reports/$($Report.id)/ExportTo"
$Export = Invoke-RestMethod -Uri $url -Method POST -Headers $headers -Body $body -ContentType "application/json" -UseBasicParsing

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Retrieve the export status to control the while loop

$url = "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.id)/reports/$($Report.id)/exports/$($Export.ID)"
$ExportStatus = Invoke-RestMethod -Uri $url -Method GET -Headers $headers

#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# Check export status and upon completion begin the report export

$ex = 0

while($ex -eq 0)
{ Start-Sleep -Seconds 30 # wait for seconds to avoid hammering the api
if ($ExportStatus.status -ne 'Succeeded')
{
$url = "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.id)/reports/$($Report.id)/exports/$($Export.ID)"
$ExportStatus = Invoke-RestMethod -Uri $url -Method GET -Headers $headers
Write-Host "File Not Ready."
}
else
{
$url = "https://api.powerbi.com/v1.0/myorg/groups/$($workspace.id)/reports/$($Report.id)/exports/$($Export.ID)/file"
Invoke-RestMethod -Uri $url -Method GET -Headers $headers -OutFile $exportfile
Write-Host "Saving File."
$ex = $ex + 1
}
}