I gave up and went with using the API through the python tool. Here is a basic implementation (follow the same steps as with the dataverse connector for setting up the app in Azure)
The retrieve dataverse function will loop through each page to give you a complete list. I havent fully tested it yet, so let me know if you spot anything wrong!
from ayx import Alteryx
import requests
import json
import pandas as pd
# Dataverse Web API endpoint
dataverse_api_url = "https://FILL IN THE CORRECT INSTANCE.dynamics.com/api/data/v9.1/"
# Dataverse authentication credentials
client_id = GET THIS FROM AZURE
client_secret = GET THIS FROM AZURE
tenant_id = GET THIS FROM AZURE
page_size = 5000
# Authenticate with Dataverse
auth_url = f"https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token"
resource_identifier = "ENTER YOUR INSTANCE URL HERE"
payload = {
"grant_type": "client_credentials",
"client_id": client_id,
"client_secret": client_secret,
"scope": f"{resource_identifier}/.default"
}
response = requests.post(auth_url, data=payload)
access_token = response.json()["access_token"]
# Set up the API request headers
headers = {
"Authorization": f"Bearer {access_token}",
"OData-MaxVersion": "4.0",
"OData-Version": "4.0",
"Accept": "application/json",
"Content-Type": "application/json; charset=utf-8",
"Prefer": f"odata.maxpagesize={page_size}"
}
def retrieve_dataverse_data(dataverse_api_url, entity_set, headers, query_params):
"""
Retrieve data from the Dataverse API and return a Pandas DataFrame.
Args:
dataverse_api_url (str): The base URL of the Dataverse API.
entity_set (str): The name of the entity set to retrieve data from.
headers (dict): The headers to use for the API request.
query_params (dict): The query parameters to use for the API request.
Returns:
pd.DataFrame: A Pandas DataFrame containing the retrieved data.
"""
api_url = f"{dataverse_api_url}{entity_set}"
response = requests.get(api_url, headers=headers, params=query_params)
# Process the API response and create a Pandas DataFrame
data = response.json()["value"]
df_final = pd.DataFrame(data)
next_link = response.json().get('@odata.nextLink', None)
counter = 1
while next_link:
print(f"Counter: {counter}")
counter += 1
response = requests.get(next_link, headers=headers)
data = response.json()["value"]
df = pd.DataFrame(data)
df_final = pd.concat([df_final, df], ignore_index=True)
next_link = response.json().get('@odata.nextLink', None)
return df_final
# Get accounts data
accounts_data = retrieve_dataverse_data(
dataverse_api_url=dataverse_api_url,
entity_set="accounts",
headers=headers,
query_params = {
"$select": "*"
}
)
Alteryx.write(accounts_data, 1)
