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

[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!

[kushanNa · Accepted solution]

Hi @AkhilGrandhi1 

I did a test on this and it's working , & this is a general pro workspace , not a permium one 

following is the test python code 

import adodbapi

# Connection string (edit table and workspace as needed)
conn = adodbapi.connect("Provider=MSOLAP.8; \
    Data Source='powerbi://api.powerbi.com/v1.0/myorg/Testws'; \
    Initial Catalog='db1'")

# Create cursor
cursor = conn.cursor()

# Replace 'YourTableName' with a real table name in your data model
dax_query = "EVALUATE 'table'"

print("Querying table: table")
cursor.execute(dax_query)

# Fetch column names
columns = [column[0] for column in cursor.description]
print("Columns:", columns)

# Fetch all rows
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close the connection
cursor.close()
conn.close()

[v-mdharahman]

Hi @AkhilGrandhi1,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you want to connect to your PowerBi Service dataset from your local Python environment, without using Notebook. As @kushanNa already responded to your query, please go through his response and check if it solves your issue.

I would also take a moment to thank @kushanNa, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[AkhilGrandhi1]

Hi @v-mdharahman 

Thanks for your reply, and thank you @kushanNa for your response as well.

The actual use case I'm working on is a bit different. I want to connect to a Power BI dataset that is already published to a Power BI Service workspace, and pull data directly from that workspace.

There’s a package called pyadomd that seems like it should support this use case, but unfortunately, it’s not working for me currently.

I’m still exploring other possible approaches. If you come across anything helpful, please feel free to share.

Thanks 

Akhil Grandhi

[v-mdharahman]

Hi @AkhilGrandhi1,

Thanks for following up and sharing the details. I see you're trying to directly connect to a Power BI dataset published in the Power BI Service using Python is definitely a valid use case, but it comes with some challenges.

The pyadomd package does support querying tabular models via XMLA endpoints, but it doesn't natively handle the authentication required for Power BI Service, especially with OAuth2 tokens. Power BI datasets hosted in the service (particularly in Premium or PPU workspaces) can expose XMLA endpoints, but any client tool needs to handle Azure AD authentication properly to access them and that's the main sticking point with pyadomd right now.

At the moment, there isn’t an officially supported or straightforward way to use Python to directly query datasets in the Power BI Service, unless you're working within an environment like Microsoft Fabric, where notebooks can natively connect to Power BI datasets.

That said, some customers explore workarounds using the Power BI REST API to extract data or exporting report content, but these come with their own limitations and setup overhead.

If a direct Python connection is critical for your workflow, you might want to look into using Power BI-connected Excel workbooks as a bridge or exploring Power BI's export/report APIs depending on the volume and type of data you need.

Best Regards,
Hammad.

[v-mdharahman]

Hi @AkhilGrandhi1,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[kushanNa]

hi @AkhilGrandhi1 

you should be able to do this , just simiply open the Python script connector and past your python code in there . 

you can try this sample code data pull from a public API 

import pandas as pd
import requests

# Define the API endpoint
url = "https://jsonplaceholder.typicode.com/posts"

# Send a GET request to the API
response = requests.get(url)

# Check if the request was successful
if response.status_code == 200:
    data = response.json()  # Parse JSON response
    df = pd.DataFrame(data)  # Convert to DataFrame for Power BI
else:
    df = pd.DataFrame({'error': [f'Failed to fetch data: {response.status_code}']})

# Output for Power BI
df

and also follow this patricks video , he also explains the basic stuff nicely https://www.youtube.com/watch?v=3_DOF_qjguA&ab_channel=GuyinaCube 

[AkhilGrandhi1]

Hi @kushanNa 

This approach wont work for me.

[kushanNa]

Hi @AkhilGrandhi1 

Apologise for the misunderstanding 

I think this is what you need ? but unfortunately I'm using a free account, so I don't have a premium space to test this 

https://stackoverflow.com/questions/56347765/connect-to-power-bi-xmla-endpoint-with-python 

https://stackoverflow.com/questions/56075159/convert-listadodbapi-apibase-sqlrow-to-pd-dataframe 

https://www.reddit.com/r/PowerBI/comments/1bbug56/automate_export_of_daily_transactions_run_a_query/... 

[kushanNa · Accepted solution]

Hi @AkhilGrandhi1 

I did a test on this and it's working , & this is a general pro workspace , not a permium one 

following is the test python code 

import adodbapi

# Connection string (edit table and workspace as needed)
conn = adodbapi.connect("Provider=MSOLAP.8; \
    Data Source='powerbi://api.powerbi.com/v1.0/myorg/Testws'; \
    Initial Catalog='db1'")

# Create cursor
cursor = conn.cursor()

# Replace 'YourTableName' with a real table name in your data model
dax_query = "EVALUATE 'table'"

print("Querying table: table")
cursor.execute(dax_query)

# Fetch column names
columns = [column[0] for column in cursor.description]
print("Columns:", columns)

# Fetch all rows
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close the connection
cursor.close()
conn.close()

[AkhilGrandhi1 · Accepted solution]

Hi @kushanNa 

Thanks for this approach — it's working fine. A couple of key points to keep in mind:

Use a lower version of Python (e.g., 3.7), as the latest versions are not supported.

Avoid spaces in the workspace name, as this is causing issues.

Currently, it uses SSO authentication, but I’m looking to use an Azure Service Principal (SPN) instead, as I’m building an application. If you know of any method to bypass SSO authentication or enable SPN-based auth, please share it.

Thanks!
Akhil Grandhi

[AkhilGrandhi1]

Hi @kushanNa 

Thanks for this approach — it's working fine. A couple of key points to keep in mind:

Use a lower version of Python (e.g., 3.7), as the latest versions are not supported.

Avoid spaces in the workspace name, as this is causing issues.

Currently, it uses SSO authentication, but I’m looking to use an Azure Service Principal (SPN) instead, as I’m building an application. If you know of any method to bypass SSO authentication or enable SPN-based auth, please share it.

Thanks!
Akhil Grandhi

[kushanNa]

Hi @AkhilGrandhi1 

I think i found a service principal based solution for this , but since you have marked your comment as an answer could you please start a new thread and provide me the link ? I will provide my findings in there , thanks for your understanding 🙂 

[AkhilGrandhi1]

https://community.fabric.microsoft.com/t5/Service/How-to-Connect-to-Power-BI-Service-Datasets-and-Pu...

[AkhilGrandhi1]

Sure

[AkhilGrandhi1]

Hi @kushanNa 

I'm trying to run this code but encountering the following error. Could you please clarify how the authentication process works? Can I directly copy and paste the connection string, or is there any configuration like python version or .net version etc or permission I need to set to execute this script successfully?

[kushanNa]

Hi @AkhilGrandhi1 

What dose the error says ? should be in the far right corner

A usual Microsoft authentication login should pop up once the code run successfully , that's how it authentication with your fabric account 

make sure you have correct workspace name in here (testws) change it to yours (db1) change it to your dataset name

Data Source='powerbi://api.powerbi.com/v1.0/myorg/Testws'; \
    Initial Catalog='db1'")

and also change the table name to your table

# Replace 'YourTableName' with a real table name in your data model
dax_query = "EVALUATE 'table'"