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