Forum Discussion
How to Connect to Power BI Service Datasets and Pull Data Using Python Locally (Outside Fabric)
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!
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
8 Replies
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 Learn
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.
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.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
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.
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 BIThe 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.
