Get Power BI Dataset Refresh History using Python

---

USE PYTHON TO GET DATA FROM POWER BI APIs

…such as limited dataset refresh history for datasets a service principle has access to

---

IN THIS ARTICLE

1. Get Dataset Refresh History from Power BI Admin API using Python
   Note: This is limited to the last 7 days or 60 refreshes, per dataset! If you want a long-running refresh history, you need to employ other methods, such as Azure Log Analytics, another endpoint, etc.

View fullsize

Example result dataframe for dataset refreshes obtained with the Admin API. The result is not a fully normalized dataframe, yet.

---

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 Dataset Refreshes
#########################################################################################

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
topn = '$top=5000'
admin_refreshables = f'{base_url}admin/capacities/refreshables?{topn}'
admin_refreshables_expand = f'{base_url}admin/capacities/refreshables?$expand=capacity,group&{topn}'

# HTTP GET Request
refreshes = requests.get(admin_refreshables_expand, headers=header)

print(refreshes)
try:
    refreshes = json.loads(refreshes.content)
except Exception as e:
    print(e)

try:
    result = pd.concat([pd.json_normalize(x) for x in refreshes['value']])
    print('Found', len(result['id'].dropna().unique().tolist()), 'refreshes.')
except Exception:
    print('No refreshes found.')

result

---

Code is provided as-is with no guarantees or warranties of any kind. Something I did quickly in spare time.

---