Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello !
I'm trying to test REST API for export to file, i am facing the below issue#403 for all types of file. I'm trying this in PowerShell script.
I've Power BI Preimum and I'm admin on the workspace.
Code:
# Calls the Active Directory Authentication Library (ADAL) to authenticate against AAD
function GetAuthToken
{
if(-not (Get-Module AzureRm.Profile)) {
Import-Module AzureRm.Profile
}
$redirectUri = "urn:ietf:wg:oauth:2.0:oob"
$resourceAppIdURI = "https://analysis.windows.net/powerbi/api"
$authority = "https://login.microsoftonline.com/common/oauth2/authorize";
$authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority
$authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $redirectUri, "Auto")
return $authResult
}
# Get the auth token from AAD
$token = GetAuthToken
# Building Rest API header with authorization token
$authHeader = @{
'Content-Type'='application/json'
'Authorization'=$token.CreateAuthorizationHeader()
}
# Refresh the dataset
#$uri = "https://api.powerbi.com/v1.0/$groupsPath/datasets/$datasetID/refreshes"
#Invoke-RestMethod -Uri $uri –Headers $authHeader –Method POST –Verbose
$uri = “https://api.powerbi.com/v1.0/myorg/groups/$Group_ID/reports/$Report_ID/ExportTo”
$body = “{`”format`”:`”pdf`”}”
$FileExport = Invoke-RestMethod -Uri $uri –Headers $auth_header –Method POST -body $body
Error Detail
Invoke-RestMethod : The remote server returned an error: (403) Forbidden.
At line:74 char:15
+ ... ileExport = Invoke-RestMethod -Uri $uri –Headers $auth_header –Method ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
Solved! Go to Solution.
# 1. export request URI
$uri = “https://api.powerbi.com/v1.0/myorg/groups/$groupsPath/reports/$reportID/ExportTo”
$body = "{ format : `"PDF`” }"
# issue the export request
$FileExport = Invoke-RestMethod -Uri $uri –Headers $authHeader –Method POST -body $body
#save the ID - we need it later
$exportId = $FileExport.id
# 2. export request status URI
$uri = "https://api.powerbi.com/v1.0/myorg/groups/$groupsPath/reports/$reportID/exports/$exportId"
$percentComplete = 0
# repeat rendering status check until status is Succeeded
while ($percentComplete -lt 100) {
Start-Sleep -Seconds 30
$exportStatus = Invoke-RestMethod -Uri $uri –Headers $authHeader –Method GET
$percentComplete = $exportStatus.percentComplete
}
# 3. retrieve the rendered file
$uri = "https://api.powerbi.com/v1.0/myorg/groups/$groupsPath/reports/$reportID/exports/$exportId/file"
$file = Invoke-RestMethod -Uri $uri –Headers $authHeader –Method GET
if you want a shortcut and you have curl installed you can replace the last line with
curl -o "test.pdf" -H $authHeader $uri
Documentation is here Reports - Export To File In Group (Power BI Power BI REST APIs) | Microsoft Docs and here Filter a report using query string parameters in the URL - Power BI | Microsoft Docs
Note that you can only include a single report level filter.
You probably want to wait for the refresh to finish before running the export. In your current process you will most likely export stale data.
Use the API sandbox to test your steps and see where the permissions break down.
The sand box says the format should be
{
format: "pdf"
}
No quotes around format.
Hi Ibendlin,
Thanks for your reply. I tried $body={ format: "pdf" } and getting the same error
Invoke-RestMethod : The remote server returned an error: (403) Forbidden.
At line:75 char:15
+ ... ileExport = Invoke-RestMethod -Uri $uri –Headers $auth_header –Method ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
I ran through the whole convoluted process and it worked fine (for varying shades of "fine") on my side. Note that I am not a tenant admin BUT I am admin of the workspace where I want to extract the report from. Are you able to access the actual report in the Power BI service?
Hi Ibendlin
I'm admin on this workspace and able to acces the actual report in Power BI Service.
Thanks,
and in the Azure portal you have set your app's authentication to Mobile and Desktop, and the app's API permissions include the Report.Read.All , right?
Hi Ibendlin,
Yes all the permission are selected including Report.Read.All
what about the authentication?
Can you run any other API commands, like dataset refresh , or pulling refresh history?
Hi ibendlin
I am able to run dataset refresh of the same report with same Azure App. But export to PDF is giving error
i think i found it. You have a typo / variable mismatch between authHeader and auth_header.
also, $body should be
$body = "{ format : `"PDF`” }"
Hi Ibendlin,
Thanks for pointing out the type. Everthing else is working fine now don't know why it's giving error on last line.
Code:
# Get the auth token from AAD
$token = GetAuthToken
# Building Rest API header with authorization token
$authHeader = @{
'Content-Type'='application/json'
'Authorization'=$token.CreateAuthorizationHeader()
}
# properly format groups path
$groupsPath = ""
if ($groupID -eq "me") {
$groupsPath = "myorg"
} else {
$groupsPath = "myorg/groups/$groupID"
}
# Export File to pdf
$uri = "https://api.powerbi.com/v1.0/$groupsPath/reports/$Report_ID/ExportTo”
$body = "{ format : `"pdf`” }"
$FileExport = Invoke-RestMethod -Uri $uri –Headers $authHeader –Method POST -body $body
# Get File Status
$id = $FileExport.id
$uri = “https://api.powerbi.com/v1.0/$groupsPath/reports/$Report_ID/Exports/$id”
$FileStatus = Invoke-RestMethod -Uri $uri –Headers $authHeader –Method GET
# Download the file
$uri = "https://api.powerbi.com/v1.0/$groupsPath/reports/$Report_ID/Exports/$id/file"
$File = Invoke-RestMethod -Uri $uri –Headers $authHeader –Method GET -OutFile $Folder+"_new.pdf"
Error:
invoke-RestMethod : The remote server returned an error: (400) Bad Request.
At C:\Users\mtalib002\Desktop\EP2\DevMyMarketPartners.ps1:95 char:10
+ $File = Invoke-RestMethod -Uri $uri –Headers $authHeader –Method GET ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand
That's not how the API works. After issuing the export request you need to monitor the rendering status, and upon completion of the rendering you can then pull the file with the final API call.
Hi Ibendlin,
Thank you very much for your help. Much appericated.
How would I get the status that file is rendered successfully and ready for download?
Thanks
Mtalib
# 1. export request URI
$uri = “https://api.powerbi.com/v1.0/myorg/groups/$groupsPath/reports/$reportID/ExportTo”
$body = "{ format : `"PDF`” }"
# issue the export request
$FileExport = Invoke-RestMethod -Uri $uri –Headers $authHeader –Method POST -body $body
#save the ID - we need it later
$exportId = $FileExport.id
# 2. export request status URI
$uri = "https://api.powerbi.com/v1.0/myorg/groups/$groupsPath/reports/$reportID/exports/$exportId"
$percentComplete = 0
# repeat rendering status check until status is Succeeded
while ($percentComplete -lt 100) {
Start-Sleep -Seconds 30
$exportStatus = Invoke-RestMethod -Uri $uri –Headers $authHeader –Method GET
$percentComplete = $exportStatus.percentComplete
}
# 3. retrieve the rendered file
$uri = "https://api.powerbi.com/v1.0/myorg/groups/$groupsPath/reports/$reportID/exports/$exportId/file"
$file = Invoke-RestMethod -Uri $uri –Headers $authHeader –Method GET
if you want a shortcut and you have curl installed you can replace the last line with
curl -o "test.pdf" -H $authHeader $uri
Hi lbendlin - thank you! That's just what I needed to finally get the PDF file. Can you, or anyone, tell me how to include a filter at runtime? I think it would be specified in $body variable (in above script), and use the "powerBIReportConfiguration" construct. But I can't find a working example.
Hi Ibendlin
Thank you so much for all your help and providing the perfect solution.
Regards,
Mtalib
Hi Ibendlin,
Could you please help me to select from the highlighted options.
1. Supported account types
2. Redirect URI (optional)
What should be the redirect URI
for my test I used "this org only" and urn:ietf:wg:oauth:2.0:oob
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.