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
  • AkhilGrandhi1's avatar
    AkhilGrandhi1
    1 year ago
    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

v-sgandrathi's avatar
v-sgandrathi
Community Support
1 year ago

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.

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    1 year ago

    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.

    • AkhilGrandhi1's avatar
      AkhilGrandhi1
      Helper I
      1 year ago
      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

      • v-sgandrathi's avatar
        v-sgandrathi
        Community Support
        1 year ago

        HI 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.

  • AkhilGrandhi1's avatar
    AkhilGrandhi1
    Helper I
    1 year ago

    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.

    • v-sgandrathi's avatar
      v-sgandrathi
      Community Support
      1 year ago

      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 BI

       

      The 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.