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!

[v-sgandrathi]

Hi @AkhilGrandhi1,

To connect to a Power BI Service dataset from a local Python environment (outside of Fabric), it is recommended to use the XMLA endpoint available in Power BI Premium or Premium Per User (PPU) workspaces. This endpoint allows external tools, including Python scripts, to query published datasets using DAX or MDX languages.

For secure authentication, the Microsoft Authentication Library (MSAL) is commonly used to acquire an Azure Active Directory (AAD) access token. This token is then passed to a query engine like pyadomd, which supports connections via the XMLA protocol.

In this setup:

• The MSAL library handles user authentication and token retrieval.
• The XMLA endpoint serves as the gateway to query the dataset directly.
• The pyadomd library executes queries against the dataset from Python.

This approach is widely adopted for scenarios where Power BI data needs to be accessed for analytics, automation, or integration workflows outside of Power BI or Fabric environments. Note that this setup requires proper workspace configuration and Azure AD app registration with the necessary API permissions.

For further reference, Microsoft provides documentation on Semantic model connectivity and management with the XMLA endpoint in Power BI - Power BI | Microsoft...

I hope this helped! Feel free to ask any further questions. If this resolved your issue, please mark it as "Accept as Solution" and give us Kudos to assist others.

Thank you.

[AkhilGrandhi1]

Hi @v-sgandrathi 

Using the XMLA endpoint directly from a Python script doesn’t seem feasible. I explored the pyadomd library, which appears to support this functionality, but unfortunately, it isn't working in my case.

Could you please help me with any Python package or approach that allows pulling data from a Power BI dataset published in the Power BI Service? If you have any related articles or documentation, kindly share them as well.

[v-sgandrathi]

Hi @AkhilGrandhi1,

Thank you for your follow-up and for clarifying the issue with the pyadomd library and XMLA endpoint. Since you’re looking for a reliable way to pull data from a Power BI dataset in the Power BI Service using Python, and you’ve requested official documentation, I recommend exploring the Power BI REST API as an alternative approach. This method is widely used, works with both Premium and non-Premium workspaces, and is well-documented by Microsoft. Below, I’ve provided official Microsoft documentation links to guide you, as requested.

Learn about the Power BI REST APIs, including how to authenticate and access datasets programmatically: Power BI REST APIs for embedded analytics and automation - Power BI REST API | Microsoft Learn

Details on using the REST API to execute DAX queries against a dataset, which is ideal for pulling data into Python: Datasets - Execute Queries - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Guidance on using the Microsoft Authentication Library (MSAL) for secure authentication with Power BI APIs: Microsoft Authentication Library (MSAL) for Python

Steps to set up an Azure AD application for Power BI API access: Register an Azure AD application to use with Power BI

The Power BI REST API with the requests and msal Python libraries is a robust solution for querying datasets.

I trust this addresses your needs. If it does, please “Accept as solution” and give it a "kudos"  to help others find it easily.

Thank you.

[AkhilGrandhi1]

Hi @v-sgandrathi 

I will check and get back to you on this

[v-sgandrathi]

Hi @AkhilGrandhi1,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Thank you.

[v-sgandrathi]

Hi @AkhilGrandhi1,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If you've already resolved the issue, you can mark the helpful reply as a "solution" so others know that the question has been answered and help other people in the community. Thank you again for your cooperation!
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.

[AkhilGrandhi1 · Accepted solution]

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()

 This approach is working for me
You can mark this as a solution 
Thanks

[v-sgandrathi]

HI @AkhilGrandhi1,

I'm glad you found a solution and resloved  the query. Thank you very much for sharing here.

Kindly mark your reply as the accepted solution so that others in the community can find it quickly.

Thankyou for connecting with Microsoft Community Forum.