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