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!
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
15 Replies
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 dfand also follow this patricks video , he also explains the basic stuff nicely https://www.youtube.com/watch?v=3_DOF_qjguA&ab_channel=GuyinaCube
Hi kushanNa
This approach wont work for me.
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
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 TeamIf 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.
