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