Tag Archives: Power BI REST API

ByHariharan Rajendran

Power BI Export Report into PDF – REST API Support

This article explains a most wanted solution in most of the organizations. Yes, it is talking about exporting a Power BI report into pdf, ppt and png. You may think that these options are already available and why I have taken this topic. Yes, we can export a report into pdf or ppt but we manually need to go to report and choose the export option to get the report in either pdf or ppt.

I will tell you how to automate this process, I mean you no need to open the report manually and export it. We also have subscription option which does the same but not in an effective way. I would say still (when am writing this article). Power BI does not have a proper subscription feature. There are lot of limitations. Check Power BI Subscription in Microsoft documentation.

This article refers the Power BI REST API which release by Power BI team recently for exporting the report into pdf, ppt and png. Refer – https://powerbi.microsoft.com/en-us/blog/export-report-to-pdf-pptx-and-png-files-using-power-bi-rest-api/

I would say this is fantastic update from Power BI team which enables us to perform so many options and it will be the alternative for proper subscription feature in Power BI.

Use Cases

  1. Build your own custom subscriptions
  2. Enable users to download their report into pdf whenever they need without opening the report.
What are those REST APIs?

We have 2 to 3 REST APIs to support following actions.

Export the file – https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports/{reportId}/ExportTo

Check the Status – https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports/{reportId}/exports/{exportId}

Download the file – https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports/{reportId}/exports/{exportId}/file

The above all REST APIs are support in both My Workspace and App workspace but the workspace should reside in Power BI Premium Capacity.

The Following article explains how to use these REST APIs in C# code – https://docs.microsoft.com/en-us/power-bi/developer/embedded/export-to

Let me explain, how to use PowerShell script to perform export the file and download the file.

What you need to test?
  1. Register an Azure APP and get the Client ID – https://dev.powerbi.com/apps
  2. Power BI Premium Capacity to your workspace – You can use your Power BI Premium Capacity or Azure Power BI Embedded Capacity.
  3. Sample report published into your workspace
  4. Get Workspace & Report ID

Open your Power BI report in browser and get the id.

In the above image, id after groups is Workspace ID or GroupID and Report ID is available after Reports.

Export a File

Use the below URL,

https://api.powerbi.com/v1.0/myorg/groups/$Group_ID/reports/$Report_ID/ExportTo

Replace the $Group_ID and $Report_ID with whatever you got from your report URL.

Export file REST API is a POST method and it also requires Body when we request the URL. I have using pdf format.

Refer the below PowerShell script.

$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

 Get File Status

Above step triggers the export file job, we need to know the status of the export. If the export status is completed means the file is ready to download.

This is a GET method.

Get the Export file id from above PowerShell script and use it in below REST API.

Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET

Use the below PowerShell script.

$id = $FileExport.id

$uri = “https://api.powerbi.com/v1.0/myorg/groups/$Group_ID/reports/$Report_ID/Exports/$id”

$FileStatus = Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET

Get or Download a file

Again it is using GET method. Following URL is the REST API URL.

https://api.powerbi.com/v1.0/myorg/groups/$Group_ID/reports/$Report_ID/Exports/$id/file

Here $id is Export file id which we get from Export file section.

Use the below PowerShell script.

$uri = “https://api.powerbi.com/v1.0/myorg/groups/$Group_ID/reports/$Report_ID/Exports/$id/file”

$File = Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET -OutFile $Folder+”_new.pdf”

I am using -OutFile option to get the file stored in local folder with filename with extension as .pdf.

Get the full script from my GitHub Repository.

Next article, I will cover the Use cases with this REST API.

ByHariharan Rajendran

Power BI REST API in PowerShell with No User Credentials pop up

In this post, I am explaining how to run the Power BI REST API based PowerShell scripts without getting a credentials pop-up window.
If you want to schedule a PowerShell file then it should not show pop-up then only it will work on windows job scheduler.
If you see the below screen, I was trying to run the PowerShell scripts and got pop-up because I used only Client ID and not passed the Username and Password.

I used the below function.
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
}

Let me show how to run the script without getting the credentials pop-up window.
We need to modify the above GetAuthToken with below script.
function GetAuthToken

{
$username = “youremailaddress”
$password = ConvertTo-SecureString “password!” –asplaintext –force

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”
$credentials = New-Object System.Management.Automation.PSCredential $Username,$password
$AADcredential = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential” -ArgumentList $credentials.UserName,$credentials.Password
$authority = “https://login.microsoftonline.com/common/oauth2/authorize”;
$authContext = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext” -ArgumentList $authority
$authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId,$AADcredential)
return $authResult
}

In the above script, I have introduced 2 variables $username and $password and hard-coded the credentials on the script itself.
Again another set of variables to handle the username and password $Credentials & $AADCredentials.
Use the above code on your script and it will not ask pop-up any window. You can use the PowerShell script file on the job scheduler.

Happy Coding!!!

ByHariharan Rajendran

Power BI Inventory using Power BI REST API

This blog post explains how the REST API can be used to prepare the Power BI Inventory Report. On this inventory report, we can show below items which almost covered all the contents from Power BI.

  1. List of Workspaces
  2. List of Datasets
  3. List of Reports
  4. List of Dashboards
  5. List of Tiles
  6. List of Data Gateways
  7. List of Data sources on the gateway
  8. List of users and their access.

 

Those REST APIs are wrapped into PowerShell script and exporting the result as csv files. You can schedule this PowerShell to get the latest contents from Power BI on a daily basis.

Pre-Requisite:

  1. Register an app and get the Client ID and Use it on the below script

 


# Parameters - fill these in before running the script!

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

 

# AAD Client ID

# To get this, go to the following page and follow the steps to provision an app

# https://dev.powerbi.com/apps

# Level of access: check all boxes

 

$clientId = "xxxxxx-xxxx-xxx-xxx-xxx"

 # End Parameters =======================================

 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

}

 function get_groups_path($group_id) {

    if ($group_id -eq "me") {

        return "myorg"

    } else {

        return "myorg/groups/$group_ID"

    }

}

 # PART 1: Authentication

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

$token = GetAuthToken

 # Building Rest API header with authorization token

$auth_header = @{

   'Content-Type'='application/json'

   'Authorization'=$token.CreateAuthorizationHeader()

}

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

# Get the list of groups

$uri = "https://api.powerbi.com/v1.0/myorg/groups/"

$all_groups = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

 $filePath = ("C:\Users\Hari\Documents\Allgroups.csv")

$all_groups | Export-csv $filePath

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

# Get the list of gateways

$uri = "https://api.powerbi.com/v1.0/myorg/gateways/"

$all_gateways = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

 $filePath = ("C:\Users\Hari\Documents\AllGateways.csv")

$all_gateways | Export-csv $filePath

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

# Get the list of gateways data sources

$uri = "https://api.powerbi.com/v1.0/myorg/gateways/"

$all_gateways = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

$all_gateways_id=$all_gateways.id

 foreach ($gateway in $all_gateways_id)

{

     $uri = "https://api.powerbi.com/v1.0/myorg/gateways/$gateway/datasources"

   $all_gateway_DS = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

  }

$filePath = ("C:\Users\Hari\Documents\AllGatewaysDS.csv")

$all_gateway_DS | Export-csv $filePath

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

# Get the list of datasets from Groups

$all_datasets=@()

foreach ($group in $all_groups)

   $group_id = $group.id

     $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/datasets"

   $datasets = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   $all_datasets+=$datasets

  }

$filePath = ("C:\Users\Hari\Documents\alldatasets.csv")

$all_datasets| Export-csv $filePath

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

# Get the list of reports from Groups

$all_reports=@()

foreach ($group in $all_groups)

   $group_id = $group.id

     $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/reports"

   $reports = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   $all_reports+=$reports

  }

$filePath = ("C:\Users\Hari\Documents\allreports.csv")

$all_reports| Export-csv $filePath

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

# Get the list of dashboards from Groups

 $output_dashboard = foreach ($group in $all_groups)

   $group_id = $group.id

   $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/dashboards"

   $all_dashboards = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   New-Object -Typename PSObject -Property @{

   Group_Id = $group.id

   GroupName = $group.name

   displayName = ($all_dashboards.displayName -join ',')

     } | Select-Object Group_Id,GroupName, DisplayName

     }

$filePath = ("C:\Users\Hari\Documents\alldashboards.csv")

$output_dashboard| Export-csv $filePath

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

# Get the list of tiles

$all_tiles=@()

foreach ($group in $all_groups)

   $group_id = $group.id

    $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/dashboards"

   $all_dashboards = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   foreach ($dashboard in $all_dashboards)

   {

     $dashboard_id = $dashboard.id

   $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/dashboards/$dashboard_id/tiles"

   $tiles = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   }

   $all_tiles+=$tiles

  }

$filePath = ("C:\Users\Hari\Documents\alltiles.csv")

$all_tiles| Export-csv $filePath

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

# Get the list of users from Groups

 $output = foreach ($group in $all_groups)

   $group_id = $group.id

   $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/users"

   $all_group_user = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

   New-Object -Typename PSObject -Property @{

   GroupName = $group.name

   Access = ($all_group_user.groupUserAccessRight -join ',')

   DisplayName = ($all_group_user.displayName -join ',')

   PricipalType = ($all_group_user.principalType -join ',')

   } | Select-Object GroupName,Access, DisplayName, PricipalType

     }

    

$filePath = ("C:\Users\Hari\Documents\AllUsers.csv")

$output | Export-csv $filePath

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

## Get the list of datasets from Groups with Group Names

 

$alldatasets_GP = foreach ($group in $all_groups)

   $group_id = $group.id

  $group_name = $group.name

   $uri = "https://api.powerbi.com/v1.0/myorg/groups/$group_id/datasets"

   $alldatasetsGP = (Invoke-RestMethod -Uri $uri –Headers $auth_header –Method GET).value

       New-Object -Typename psobject -Property @{

   GroupId = $group.id

   GroupName = $group.name

   Dataset_Id = ($alldatasetsGP.id -join ',')

   DisplayName = ($alldatasetsGP.name -join ',')

     } | Select-Object GroupName,Dataset_Id, DisplayName

 }

$filePath = ("C:\Users\Hari\Documents\alldatasets_Groups.csv")

$alldatasets_GP | Export-csv $filePath

[/]
1