Get Workspaces from Power BI API with Python
USE PYTHON TO GET DATA FROM POWER BI APIs
…such as the workspaces that a user or service principle has access to
IN THIS ARTICLE
Get Workspaces from Power BI Admin API using Python
In the linked Github Gist
Get Artifacts in Workspaces (Datasets, Reports, Dataflows…)
Get Users with roles in Workspaces
HOW TO SET IT UP?
I wrote a post about setting up a Service Principal, permissions & settings to get you started, as well as explaining the Authentication. You can find that post, here.
#########################################################################################
# Authentication - Replace string variables with your relevant values
#########################################################################################
import json, requests, pandas as pd
try:
from azure.identity import ClientSecretCredential
except Exception:
!pip install azure.identity
from azure.identity import ClientSecretCredential
# --------------------------------------------------------------------------------------#
# String variables: Replace with your own
tenant = 'Your-Tenant-ID'
client = 'Your-App-Client-ID'
client_secret = 'Your-Client-Secret-Value' # See Note 2: Better to use key vault
api = 'https://analysis.windows.net/powerbi/api/.default'
# --------------------------------------------------------------------------------------#
# Generates the access token for the Service Principal
auth = ClientSecretCredential(authority = 'https://login.microsoftonline.com/',
tenant_id = tenant,
client_id = client,
client_secret = client_secret)
access_token = auth.get_token(api)
access_token = access_token.token
print('
Successfully authenticated.')
#########################################################################################
# Note 1: This code was authored for use in a Google Colab Notebook
# Note 2: Check here for info on using Azure Key Vault:
# https://docs.microsoft.com/en-us/azure/key-vault/secrets/quick-create-python
# The above code should be modified to programmatically get the secret from AKV
#########################################################################################
# Admin: Get all Workspaces only - see Gist for all workspaces + expand
#########################################################################################
base_url = 'https://api.powerbi.com/v1.0/myorg/'
header = {'Authorization': f'Bearer {access_token}'}
# The admin API doesn't work unless you provide a ?$top=n argument
# Top 5000 workspaces
topn = '$top=5000'
# Get workspaces only
admin_groups = f'{base_url}admin/groups?{topn}'
# HTTP GET Request
groups = requests.get(admin_groups, headers=header)
# Response code (200 = Success; 401 = Unauthorized; 404 = Bad Request)
print(groups)
try:
groups = json.loads(groups.content)
except Exception as e:
print(e)
exit()
#########################################################################################
# Return the results in dataframes
#########################################################################################
try:
result = pd.concat([pd.json_normalize(x) for x in groups['value']])
print('Found', len(result['id'].dropna().unique().tolist()), 'workspaces.')
except Exception:
print('No workspaces found.')
Code is provided as-is with no guarantees or warranties of any kind. Just something I did quickly in spare time.