Forum Discussion

AkhilGrandhi1's avatar
AkhilGrandhi1
Helper I
1 year ago
Solved

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...
Need Help
  • kushanNa's avatar
    kushanNa
    1 year ago

    Hi AkhilGrandhi1 

     

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

     

     

  • AkhilGrandhi1's avatar
    AkhilGrandhi1
    1 year ago

    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

     

AkhilGrandhi1's avatar
AkhilGrandhi1
Helper I
1 year ago

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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.