How to Connect to Power BI Service Datasets and Pull Data Using Python Locally (Outside Fabric) SPN

[AkhilGrandhi1]

I am trying to connect to a Power BI Service dataset from my local Python environment, without using Fabric Notebooks or Fabric Workspaces. My goal is to query and pull data from a published Power BI dataset directly into Python for further processing or integration. I have explored libraries like msal for authentication and looked into options like REST APIs and XMLA endpoints, but I’m not sure what the recommended or secure approach is to retrieve data directly from Power BI Service using Python. Has anyone successfully implemented this or can provide guidance on the proper steps, libraries, and authentication flow? Appreciate any help or suggestions from the community. Thank you!

[v-sdhruv]

Hi @AkhilGrandhi1 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[kushanNa · Accepted solution]

Hi @AkhilGrandhi1 

please try the following Power BI API approach 

before you try the code you will need to set this up the environment , step 6 and step 7 

https://learn.microsoft.com/en-us/power-bi/developer/embedded/embed-sample-for-customers?tabs=net-co...

app will need read permission : https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries

once all set try this code :

import requests
from msal import ConfidentialClientApplication
import json

# --- CONFIGURATION ---
TENANT_ID = 'your-tenant-id'
CLIENT_ID = 'your-client-id'
CLIENT_SECRET = 'your-client-secret'
WORKSPACE_ID = 'xxxx'
DATASET_ID = 'xxxx'


# --- MSAL SETUP ---
AUTHORITY = f'https://login.microsoftonline.com/{TENANT_ID}'
SCOPE = ['https://analysis.windows.net/powerbi/api/.default']

app = ConfidentialClientApplication(
    CLIENT_ID, authority=AUTHORITY, client_credential=CLIENT_SECRET
)

# --- Acquire Token ---
result = app.acquire_token_for_client(scopes=SCOPE)
if "access_token" not in result:
    raise Exception(f"Failed to get token: {result.get('error_description')}")

# --- Request Setup ---
url = f"https://api.powerbi.com/v1.0/myorg/groups/{WORKSPACE_ID}/datasets/{DATASET_ID}/executeQueries"
headers = {
    'Authorization': f"Bearer {result['access_token']}",
    'Content-Type': 'application/json'
}
payload = {
    "queries": [
        {
            "query": "EVALUATE VALUES(MyTable)"
        }
    ],
    "serializerSettings": {
        "includeNulls": True
    },
    
}

# --- Send POST Request ---
response = requests.post(url, headers=headers, json=payload)

# --- Handle Response ---
if response.status_code == 200:
    data = response.json()
    print(json.dumps(data, indent=2))
else:
    print(f"Error {response.status_code}: {response.text}")

Works for me