Learn how to retrieve a list of reports and dashboards in a PowerBI workspace

Photo by Martin Adams on Unsplash

If you manage multiple PowerBI workspaces, it can become cumbersome to keep track of the statuses, owners, and customers of reports within a workspace. One way to help with this issue is to keep an inventory of the workspace’s reports and dashboards and to visit them regularly for cleaning purposes. In this article, I will explain two ways to get a list of reports and dashboards in a PowerBI workspace.

1. Call PowerBI REST API in Databricks: You can use PowerBI REST APIs to get a list of reports in a workspace by providing the workspaceid. When you are prompted to sign in to PowerBI to try the REST call, sign in with your account or organization’s account. If you get a 403 error, try switching to different directories to see if your workspace is in another directory. This sandbox environment will generate a short-lived access token for you, which you can copy and paste into Databricks or Postman. Here is a code snippet in Python notebook in Databricks to make the call:

import requests
import json
# copy and paste your workspaceid
workspace_id_prod = "c971-70b...."

#copy and paste your access token
access_token = "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng...."

reports_url = 'https://api.powerbi.com/v1.0/myorg/groups/' + workspace_id_prod + '/reports'
header = {'Authorization': f'Bearer {access_token}','Content-Type':'application/json'}
reports = json.loads(requests.get(url=reports_url, headers=header).content)['value']

Next, create a schema to read the data.

# create a schema to read the json file

from pyspark.sql.types import StructType,StructField, StringType, BooleanType
schema = StructType([       
    StructField('id', StringType(), True),
    StructField('reportType', StringType(), True),
    StructField('name', StringType(), True),
    StructField('webUrl', StringType(), True),
    StructField('embedUrl', StringType(), True),
    StructField('isFromPbix', BooleanType(), True),
    StructField('isOwnedByMe', BooleanType(), True),
    StructField('datasetId', StringType(), True),
    StructField('datasetWorkspaceId', StringType(), True),
    StructField('users', StringType(), True),
    StructField('subscriptions', StringType(), True),
])

Next, read the data, and download it to CSV if necessary.

# create a dataframe out of the list applying the above schema
df = spark.createDataFrame(data=reports, schema=schema)
# display and download the csv file if necessary
display(df)

2. Use Postman and upload to DBFS: You can use Postman to get the results. For this, copy and paste the url and access token to Postman. Under the Headers section, create a parameter named Authorization and paste the access token including the word “Bearer”. Once you get the result, copy and save it in a file using your local code editor or Notepad. Make sure to save it in JSON format. You can then upload the file to Databricks DBFS, and read the data.

#read json file
df1 = spark.read.format("json").option("multiline","true")\
.json("dbfs:/FileStore/shared_uploads/your_user_account/powerbi_prod_workspace.json")
#display and download
display(df1)

In conclusion, these two ways are good options for quickly getting a list of reports and dashboards in a PowerBI workspace. You can use them to keep an inventory of the workspace’s reports and dashboards and to visit them regularly for cleaning purposes.

Credit: Automating PowerBI Refreshes using Databricks