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

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-core

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