Integrate SharePoint lists instead of using Onelake (EN)

Before we can integrate with the list, we need to set up a Service Principal that has permission to access and download the content. Here's how:

1. Create a Service Principal in the Entra ID (Azure Portal).
2. Add the required API permissions to the app registration:Make sure to include: Microsoft Graph > Sites.ReadWrite.AllAn admin must grant consent for these permissions.
3. Generate a client secret for the Service Principal and copy the value immediately, you won’t be able to retrieve it later.
4. Store the credentials securely:Save the Client ID, Tenant ID, and Secret Value in a secure location, preferably in Azure Key Vault.

The last step is to grant the Service Principal access to the SharePoint list. You can follow this link and make sure the permissions are correctly set:

<AppPermissionRequests AllowAppOnlyPolicy="true">

<AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="FullControl" /> </AppPermissionRequests>

Retrieving data

Now that everything’s set up, we can integrate the SharePoint list with Python in a Fabric notebook. First, we retrieve an access token from the Graph API using our stored credentials. We use the MSAL library to acquire the token via the ConfidentialClientApplication.

headers={'Authorization': f'Bearer {self.access_token}'}

Next, we retrieve the Site ID for your SharePoint environment using:

site_url =

f"https://graph.microsoft.com/v1.0/sites/root:/sites/{source_layer}"

site_resp = requests.get(site_url, headers=headers).json()

site_id = site_resp['id']

You’ll need this Site ID to correctly call your SharePoint list using the proper URL. With the site_id and the list name (used as the table name in the URL), you can fetch the data using the function below.

In this example, we select the top 999 records, since the default is only 200. We also add pagination to ensure all records are retrieved, especially useful if your list contains a lot of entries. For that, we use the @odata.nextLink property.

Although you now have the data in a list, which you can store as a Pandas DataFrame, one issue is that the column names aren’t the same as in your original SharePoint list. Instead, you’ll see generic names like title, name, field_{x}, etc., these are just default placeholders.

But the actual column names would be much more useful. You can retrieve them using the /columns endpoint:

columns_url =f"https://graph.microsoft.com/v1.0/sites/{site_id}/lists/{self.processor.source_table_name}/columns"

Once you’ve retrieved the display names of your columns via the API, you can easily rename them in your DataFrame.

Now that the data is cleaned up, you can store it in a Delta table and display it in Fabric.