How to integrate SharePoint Online with Power BI

Integrate Power BI and SharePoint Online
Integrate Power BI and SharePoint Online

Learn how to quickly and efficiently connect SharePoint Online with Power BI for smooth data access.

Read and write SharePoint Online data effortlessly. Integrate, manage, and automate sites, lists, document libraries, and files — almost no coding required. You can do it all using the high-performance SharePoint Online ODBC Driver for Power BI (often referred to as the SharePoint Online Connector). We'll walk you through the entire setup.

Ready to dive in? Download the product to jump right in, or follow the step-by-step guide below to see how it works.

Video Tutorial

This video covers the following topics and more, so please watch carefully. After watching the video, follow the steps outlined in this article:

  • How to download and install the required PowerPack for SharePoint Online integration in Power BI
  • How to configure the connection for SharePoint Online
  • Features of the ZappySys API Driver (Authentication / Query Language / Examples / Driver UI)
  • How to use the SharePoint Online in Power BI

Create data source using SharePoint Online ODBC Driver

Step-by-step instructions

To get data from SharePoint Online using Power BI, we first need to create an ODBC data source. We will later read this data in Power BI. Perform these steps:

  1. Download and install ODBC PowerPack (if you haven't already).

  2. Search for odbc and open the ODBC Data Sources (64-bit):

    Open ODBC Data Source

  3. Create a User data source (User DSN) based on the ZappySys API Driver driver:

    ZappySys API Driver
    Create new User DSN for ZappySys API Driver
    • Create and use a User DSN if the client application runs under a User Account. This is the ideal option at design time (e.g., when developing in Visual Studio). Use it for both types of applications (64-bit and 32-bit).
    • Create and use a System DSN if the client application runs under a System Account (e.g., as a Windows Service). This is usually the required option in a production environment. If your Windows Service is a 32-bit application, you must use the 32-bit ODBC Data Source Administrator to configure this
    When deployed to production, Power BI runs under a Service Account. Therefore, for the production environment, you must create and use a System DSN.

  4. When the Configuration window appears give your data source a name if you haven't done that already, then select "SharePoint Online" from the list of Popular Connectors. If "SharePoint Online" is not present in the list, then click "Search Online" and download it. Then set the path to the location where you downloaded it. Finally, click Continue >> to proceed with configuring the DSN:

    SharepointOnlineDSN
    SharePoint Online
    ODBC DSN Template Selection

  5. Now it's time to configure the Connection Manager. Select Authentication Type, e.g. Token Authentication. Then select API Base URL (in most cases, the default one is the right one). More info is available in the Authentication section.

    User Credentials

    SharePoint Online authentication

    Use delegated access (User Credentials) whenever you want to let a signed-in user work with their own resources or resources they can access. Whether it's an admin setting up policies for their entire organization or a user deleting an email in their inbox, all scenarios involving user actions should use delegated access. [API reference]

    Follow these simple steps below to create Microsoft Entra ID application with delegated access:

    WARNING: If you are planning to automate processes, we recommend that you use a Application Credentials authentication method. In case, you still need to use User Credentials, then make sure you use a system/generic account (e.g. automation@my-company.com). When you use a personal account which is tied to a specific employee profile and that employee leaves the company, the token may become invalid and any automated processes using that token will start to fail.
    1. Navigate to the Azure Portal and log in using your credentials.
    2. Access Microsoft Entra ID.

    3. Register a new application by going to App registrations and clicking on New registration button:

      Start new app registration in Microsoft Entra ID
      INFO: Find more information on how to register an application in Graph API reference.

    4. When configuration window opens, configure these fields:

      • Supported account type
        • Use Accounts in this organizational directory only, if you need access to data in your organization only.
      • Redirect URI:
        • Set the type to Public client/native (mobile & desktop).
        • Use https://zappysys.com/oauth as the URL.
      Register app in Microsoft Entra ID

    5. After registering the app, copy the Application (client) ID for later:

      Copy client ID of Microsoft Entra ID app

    6. Copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs to use later in the configuration:

      Copy Auth and Token URLs in Microsoft Entra ID app

    7. Now go to SSIS package or ODBC data source and use the copied values in User Credentials authentication configuration:

      • In the Authorization URL field paste the OAuth authorization endpoint (v2) URL value you copied in the previous step.
      • In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
      • In the Client ID field paste the Application (client) ID value you copied in the previous step.
      • In the Scope field use the default value or select individual scopes, e.g.:
        • email
        • offline_access
        • openid
        • profile
        • User.Read
        • Sites.Read.All
        • Sites.ReadWrite.All
        • Files.Read.All
        • Files.ReadWrite.All
    8. Press Generate Token button to generate Access and Refresh Tokens.
    9. Optional step. Choose Default Site Id from the drop down menu.
    10. Click Test Connection to confirm the connection is working.
    11. Done! Now you are ready to use the API Connector!
    API Connection Manager configuration

    Just perform these simple steps to finish authentication configuration:

    1. Set Authentication Type to User Credentials [OAuth]
    2. Optional step. Modify API Base URL if needed (in most cases default will work).
    3. Fill in all the required parameters and set optional parameters if needed.
    4. Press Generate Token button to generate the tokens.
    5. Finally, hit OK button:
    SharepointOnlineDSN
    SharePoint Online
    User Credentials [OAuth]
    https://graph.microsoft.com/v1.0
    Required Parameters
    Authorization URL Fill-in the parameter...
    Token URL Fill-in the parameter...
    Client ID Fill-in the parameter...
    Scope Fill-in the parameter...
    Return URL Fill-in the parameter...
    Default Site Id (select after pressing 'Generate Token') Fill-in the parameter...
    Optional Parameters
    Client Secret
    Default Drive Id (select after pressing 'Generate Token')
    Login Prompt Option
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429|503|423
    RetryCountMax 5
    RetryMultiplyWaitTime True
    Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed)
    Extra Headers (e.g. Header1:AAA||Header2:BBB)
    IsAppCred 0
    ODBC DSN OAuth Connection Configuration

    Application Credentials

    SharePoint Online authentication

    Application-only access is broader and more powerful than delegated access (User Credentials), so you should only use app-only access where needed. Use it when: 1. The application needs to run in an automated way, without user input (for example, a daily script that checks emails from certain contacts and sends automated responses). 2. The application needs to access resources belonging to multiple different users (for example, a backup or data loss prevention app might need to retrieve messages from many different chat channels, each with different participants). 3. You find yourself tempted to store credentials locally and allow the app to sign in 'as' the user or admin. [API reference]

    Follow these simple steps to create Microsoft Entra ID application with application access permissions:

    • Create an OAuth app
    • Configure App Permissions

    • Grant granular permissions to the OAuth application (optional)

      This step allows to grant OAuth application granular permissions, i.e. access configured specific Sites, Lists, and List Items.

    Step-1: Create OAuth app

    1. Navigate to the Azure Portal and log in using your credentials.
    2. Access Microsoft Entra ID.
    3. Register a new application by going to App registrations and clicking on New registration button: Start new app registration in Microsoft Entra ID
      INFO: Find more information on how to register an application in Graph API reference.

    4. When configuration window opens, configure these fields:

      • Supported account type
        • e.g. select Accounts in this organizational directory only if you need access to data in your organization only.
      • Redirect URI:
        • Set the type to Public client/native (mobile & desktop).
        • Leave the URL field empty.
      Register app in Microsoft Entra ID

    5. After registering the app, copy the Application (client) ID for later:

      Copy client ID of Microsoft Entra ID app

    6. Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs:

      Copy Auth and Token URLs in Microsoft Entra ID app

    7. Continue and create Client secret:

      Add Client secret for Microsoft Entra ID app

    8. Then copy the Client secret for later steps:

      Copy Client secret

    Step-2: Configure App Permissions

    1. Continue by adding permissions for the app by going to the API permissions section, and clicking on Add a permission:

      Start adding permissions to Microsoft Entra ID app

    2. Select Microsoft Graph:

      Select Graph API permissions for Microsoft Entra ID app

    3. Then choose Application permissions option:

      Select app permissions for Microsoft Entra ID app

    4. Continue by adding these Sites permissions (search for site):

      Select SharePoint Online application scopes
      INFO: If you want to access specific lists or list items (table-level vs row-level security) rather than the full site, then add Lists.SelectedOperations.Selected or ListItems.SelectedOperations.Selected permissions , just like in the previous step (search for list).
      WARNING: If you add any of these permissions - Sites.Selected, Lists.SelectedOperations.Selected, or ListItems.SelectedOperations.Selected - you must grant the app the SharePoint permissions for the specific resource (e.g. a Site, a List, or a ListItem). Follow instructions in Grant SharePoint permissions to the OAuth app (optional) section on how to accomplish that.

    5. Finish by clicking Add permissions button:

      Add permissions to Microsoft Entra ID app

    6. Now it's time to Grant admin consent for your application:

      Grant admin consent for Microsoft Entra ID app

    7. Confirm all the permissions are granted:

      Admin consent granted successfully in Entra ID

    8. Now go to SSIS package or ODBC data source and use the copied values in Application Credentials authentication configuration:

      • In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
      • In the Client ID field paste the Application (client) ID value you copied in the previous step.
      • In the Client Secret field paste the Client secret value you copied in the previous step.
      • Optional step. Choose Default Site Id from the drop down menu.
    9. Click Test Connection to confirm the connection is working.
    10. Done!

    Step-3 (optional): Grant SharePoint permissions to the OAuth app

    If you used Sites.Selected, Lists.SelectedOperations.Selected or ListItems.SelectedOperations.Selected permission in the previous section, you must grant the app the SharePoint permissions for the specific resource (e.g. a Site, a List, or a ListItem). You can do it using PowerShell or SharePoint admin center (obsolete method).

    Granting SharePoint permissions using PowerShell

    Unfortunately, there is no user interface available to control these permissions yet. For now, granting permissions has to be accomplished via Microsoft Graph API [Microsoft reference]:

    You must be the owner of the resource to grant permissions (i.e. belong to SharePoint owners group or be the owner of the Site or List).
    1. Open PowerShell (run as admin).

    2. Call the following PowerShell code to grant read and write permission for the app we created earlier (assuming Application (client) ID is 89ea5c94-aaaa-bbbb-cccc-3fa95f62b66e): 

      ##### CONFIGURATION ############################################################################################

# More info at:
# - https://learn.microsoft.com/en-us/graph/permissions-selected-overview?tabs=powershell
# - https://learn.microsoft.com/en-us/powershell/module/microsoft.graph.sites/?view=graph-powershell-1.0

# Find SharePoint Site Id by following these steps:
# - Login into SharePoint Online
# - Open this URL https://{your-company}.sharepoint.com/_api/site in the browser
#   NOTE: For a subsite use https://{your-company}.sharepoint.com/sites/{your-subsite}/_api/site
# - Find 'Id' element in the response (e.g. <d:Id m:type="Edm.Guid">efcdd21a-aaaa-bbbb-cccc-5d8104d8b5e3</d:Id>)
# - Copy the Site Id, i.e.: efcdd21a-aaaa-bbbb-cccc-5d8104d8b5e3 
# Set $siteId variable to the retrieved Site Id:

$siteId="efcdd21a-aaaa-bbbb-cccc-5d8104d8b5e3"


# Find your Application Id (i.e. Client Id) in the Azure Portal, in App Registrations page:
# https://portal.azure.com/#view/Microsoft_AAD_RegisteredApps/ApplicationsListBlade

$applicationId="89ea5c94-aaaa-bbbb-cccc-3fa95f62b66e"


# Set one of app permissions: read, write, fullcontrol, owner ('write' includes 'read' permission)

$appPermission="write"



##### SCRIPT ###################################################################################################

# Step-1: Install 'Microsoft.Graph.Sites' module if it's not installed
if (-not (Get-Module Microsoft.Graph.Sites -ListAvailable))
{
    Install-Module Microsoft.Graph.Sites
}

# Step-2: Load module
Import-Module Microsoft.Graph.Sites

# Step-3: Login (use Azure admin or SharePoint owner account)
DisConnect-MgGraph
Connect-MgGraph

# Step-4: Set parameters for API call (set permissions, Site ID and Application ID)

$params = @{
	roles = @($appPermission)
	grantedTo = @{
		application = @{id = $applicationId}
	}
}

# Step-5: Grant permissions
New-MgSitePermission -SiteId $siteId -BodyParameter $params

# Done!
Write-Host "Granted SharePoint permissions to application '$applicationId' for site '$siteId'."
    3. That's it! Now you can use the connector.

    Granting SharePoint permissions using SharePoint admin center (obsolete method)

    If you used Site.Selected permission you can link it SharePoint site in SharePoint admin center [SharePoint reference]. Follow these simple steps to accomplish that:

    1. Log in to SharePoint admin center using this URL: (replace YOURCOMPANY with your company name): 
      https://YOURCOMPANY-admin.sharepoint.com/_layouts/15/appinv.aspx
      INFO: To view all the registered apps in SharePoint, visit this page: https://YOURCOMPANY-admin.sharepoint.com/_layouts/15/appprincipals.aspx?Scope=Web.
    2. In the App Id field enter Application (client) ID you copied in the previous step.

    3. In the Permission Request XML field enter XML snippet which describes which SharePoint permissions you want to grant to the OAuth app, e.g.: 

      <AppPermissionRequests AllowAppOnlyPolicy="true">
  <AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="FullControl" />
</AppPermissionRequests>
      INFO: This example gives app FullControl, but you can also grant it Read or Write permissions.
    4. Click Create to grant the permission to your OAuth app.
    5. That's it! Now you can use the connector.
    API Connection Manager configuration

    Just perform these simple steps to finish authentication configuration:

    1. Set Authentication Type to Application Credentials [OAuth]
    2. Optional step. Modify API Base URL if needed (in most cases default will work).
    3. Fill in all the required parameters and set optional parameters if needed.
    4. Finally, hit OK button:
    SharepointOnlineDSN
    SharePoint Online
    Application Credentials [OAuth]
    https://graph.microsoft.com/v1.0
    Required Parameters
    Token URL Fill-in the parameter...
    Client ID Fill-in the parameter...
    Client Secret Fill-in the parameter...
    Default Site Id Fill-in the parameter...
    Optional Parameters
    Scope https://graph.microsoft.com/.default
    Default Drive Id
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429|503|423
    RetryCountMax 5
    RetryMultiplyWaitTime True
    Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed)
    Extra Headers (e.g. Header1:AAA||Header2:BBB)
    IsAppCred 1
    ODBC DSN OAuth Connection Configuration

    Application Credentials with Certificate (Sign JWT with Private Key)

    SharePoint Online authentication

    Follow these simple steps to create Microsoft Entra ID application with application access permissions:

    • Create an OAuth app
    • Configure App Permissions
    • Create Public/Private Key Pair
    • Upload Public Key
    • Configure ZappySys Connection for Private Key use

    • Grant granular permissions (optional)

      This step allows to grant OAuth application granular permissions, i.e. access configured specific Sites, Lists, and List Items.

    Step-1: Create OAuth app

    1. Navigate to the Azure Portal and log in using your credentials.
    2. Access Microsoft Entra ID.
    3. Register a new application by going to App registrations and clicking on New registration button: Start new app registration in Microsoft Entra ID
      INFO: Find more information on how to register an application in Graph API reference.
    4. When configuration window opens, configure these fields:
      • Supported account type
        • e.g. select Accounts in this organizational directory only if you need access to data in your organization only.
      Register app in Microsoft Entra ID
    5. After registering the app, copy the Application (client) ID for later: Copy client ID of Microsoft Entra ID app
    6. Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs: Copy Auth and Token URLs in Microsoft Entra ID app

    Step-2: Configure App Permissions

    1. Continue by adding permissions for the app by going to the API permissions section, and clicking on Add a permission: Start adding permissions to Microsoft Entra ID app
    2. Select Microsoft Graph: Select Graph API permissions for Microsoft Entra ID app
    3. Then choose Application permissions option: Select app permissions for Microsoft Entra ID app

    4. Continue by adding these Sites permissions (search for site):

      Select SharePoint Online application scopes
      INFO: If you want to access specific lists or list items (table-level vs row-level security) rather than the full site, then add Lists.SelectedOperations.Selected or ListItems.SelectedOperations.Selected permissions , just like in the previous step (search for list).
      WARNING: If you add any of these permissions - Sites.Selected, Lists.SelectedOperations.Selected, or ListItems.SelectedOperations.Selected - you must grant the app the SharePoint permissions for the specific resource (e.g. a Site, a List, or a ListItem). Follow instructions in Grant SharePoint permissions to the OAuth app (optional) section on how to accomplish that.
    5. Finish by clicking Add permissions button: Add permissions to Microsoft Entra ID app
    6. Now it's time to Grant admin consent for your application: Grant admin consent for Microsoft Entra ID app
    7. Confirm all the permissions are granted: Admin consent granted successfully in Entra ID

    Step-3: Generate a Self-Signed Certificate

    Now let's go through setting up a certificate-based authentication flow for Microsoft Graph or other Azure AD protected APIs using client credentials and a JWT.

    You can use OpenSSL or any other way to generate Certificate file but to make it simple we will use below example PowerShell script.

    Open PowerShell and execute code listed in below steps.

    
# Run this in PowerShell
#Change .AddYears(1) to desired number. By default it expires certificate in one year as per below code.

$cert = New-SelfSignedCertificate `
  -Subject "CN=MyClientAppCert" `
  -KeySpec Signature `
  -KeyExportPolicy Exportable `
  -KeyLength 2048 `
  -CertStoreLocation "Cert:\CurrentUser\My" `
  -KeyAlgorithm RSA `
  -HashAlgorithm SHA256 `
  -NotAfter (Get-Date).AddYears(1) `
  -Provider "Microsoft Enhanced RSA and AES Cryptographic Provider"

# Export private key (.pfx)  - Keep this with you to make API calls (SECRET KEY - DONOT SHARE)
$pfxPath = "$env:USERPROFILE\Desktop\private_key.pfx"
$pwd = ConvertTo-SecureString -String "yourStrongPassword123" -Force -AsPlainText
Export-PfxCertificate -Cert $cert -FilePath $pfxPath -Password $pwd

# Export public certificate (.cer) - UPLOAD this to Azure Portal
$cerPath = "$env:USERPROFILE\Desktop\public_key.cer"
Export-Certificate -Cert $cert -FilePath $cerPath

    Step-4: Upload the Certificate (i.e. Public Key *.cer)

    Once we have certificate file generated.
    1. In your App Registration, go to Certificates & secrets
    2. Under Certificates, click Upload certificate
    3. Select the .cer file (public certificate) Upload public key (certificate file) for App-Credentials (JWT Auth)
    4. Click Select a file (Browse button)
    5. Select public key file (*.cer) from local machine and click OK to upload

    Step-5: Configure ZappySys Connection - Use private key (i.e. *.pfx or *.pem)

    Now its time to use certificate pfx file (private key) generated in the previous step (NOTE: PFX file contains both private key and public key).
    1. Go to SSIS package or ODBC data source and use the copied values in Application Credentials authentication configuration:
      • In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
      • In the Client ID field paste the Application (client) ID value you copied in the previous step.
    2. Configure private key
      • go to Certificate Tab
      • Change Storage Mode to Disk File: *.pfx format (PKCS#12).

        NOTE: You can also use Stored In LocalMachine mode if PFX file already imported in the Local Certificate Storage Area - User Store OR Machine Store.

        If you used OpenSSL to generate key pair then use Disk File: *.pem format (PKCS#8 or PKCS#1) Mode for Cert Store Location.

      • Supply the key file path
      • Supply the certificate password (same password used in earlier PowerShell script)
    3. Now go back to General Tab, choose Default Site Id and Default Drive Id from the drop down menu.
    4. Click Test connection see everything is good

    Step-6 (optional): Grant granular permissions to the OAuth app

    If you used Sites.Selected, Lists.SelectedOperations.Selected or ListItems.SelectedOperations.Selected permission in the previous section, you must grant the app the SharePoint permissions for the specific resource (e.g. a Site, a List, or a ListItem). You can do it using PowerShell or SharePoint admin center (obsolete method).

    Granting SharePoint permissions using PowerShell

    Unfortunately, there is no user interface available to control these permissions yet. For now, granting permissions has to be accomplished via Microsoft Graph API [Microsoft reference]:

    You must be the owner of the resource to grant permissions (i.e. belong to SharePoint owners group or be the owner of the Site or List).
    1. Open PowerShell (run as admin).

    2. Call the following PowerShell code to grant read and write permission for the app we created earlier (assuming Application (client) ID is 89ea5c94-aaaa-bbbb-cccc-3fa95f62b66e): 

      ##### CONFIGURATION ############################################################################################

# More info at:
# - https://learn.microsoft.com/en-us/graph/permissions-selected-overview?tabs=powershell
# - https://learn.microsoft.com/en-us/powershell/module/microsoft.graph.sites/?view=graph-powershell-1.0

# Find SharePoint Site Id by following these steps:
# - Login into SharePoint Online
# - Open this URL https://{your-company}.sharepoint.com/_api/site in the browser
#   NOTE: For a subsite use https://{your-company}.sharepoint.com/sites/{your-subsite}/_api/site
# - Find 'Id' element in the response (e.g. <d:Id m:type="Edm.Guid">efcdd21a-aaaa-bbbb-cccc-5d8104d8b5e3</d:Id>)
# - Copy the Site Id, i.e.: efcdd21a-aaaa-bbbb-cccc-5d8104d8b5e3 
# Set $siteId variable to the retrieved Site Id:

$siteId="efcdd21a-aaaa-bbbb-cccc-5d8104d8b5e3"


# Find your Application Id (i.e. Client Id) in the Azure Portal, in App Registrations page:
# https://portal.azure.com/#view/Microsoft_AAD_RegisteredApps/ApplicationsListBlade

$applicationId="89ea5c94-aaaa-bbbb-cccc-3fa95f62b66e"


# Set one of app permissions: read, write, fullcontrol, owner ('write' includes 'read' permission)

$appPermission="write"



##### SCRIPT ###################################################################################################

# Step-1: Install 'Microsoft.Graph.Sites' module if it's not installed
if (-not (Get-Module Microsoft.Graph.Sites -ListAvailable))
{
    Install-Module Microsoft.Graph.Sites
}

# Step-2: Load module
Import-Module Microsoft.Graph.Sites

# Step-3: Login (use Azure admin or SharePoint owner account)
DisConnect-MgGraph
Connect-MgGraph

# Step-4: Set parameters for API call (set permissions, Site ID and Application ID)

$params = @{
	roles = @($appPermission)
	grantedTo = @{
		application = @{id = $applicationId}
	}
}

# Step-5: Grant permissions
New-MgSitePermission -SiteId $siteId -BodyParameter $params

# Done!
Write-Host "Granted SharePoint permissions to application '$applicationId' for site '$siteId'."
    3. That's it! Now you can use the connector.
    API Connection Manager configuration

    Just perform these simple steps to finish authentication configuration:

    1. Set Authentication Type to Application Credentials with Certificate (Sign JWT with Private Key) [OAuth]
    2. Optional step. Modify API Base URL if needed (in most cases default will work).
    3. Fill in all the required parameters and set optional parameters if needed.
    4. Finally, hit OK button:
    SharepointOnlineDSN
    SharePoint Online
    Application Credentials with Certificate (Sign JWT with Private Key) [OAuth]
    https://graph.microsoft.com/v1.0
    Required Parameters
    Token URL Fill-in the parameter...
    Client ID Fill-in the parameter...
    Certificate: *** Configure [Client Certificate] Tab *** Fill-in the parameter...
    Default Site Id Fill-in the parameter...
    Optional Parameters
    Default Drive Id
    RetryMode RetryWhenStatusCodeMatch
    RetryStatusCodeList 429|503|423
    RetryCountMax 5
    RetryMultiplyWaitTime True
    Search Option For Non-Indexed Fields (Default=Blank - Search Only Indexed)
    Extra Headers (e.g. Header1:AAA||Header2:BBB)
    IsAppCred 1
    ODBC DSN OAuth Connection Configuration

  6. Once the data source connection has been configured, it's time to configure the SQL query. Select the Preview tab and then click Query Builder button to configure the SQL query:

    ZappySys API Driver - SharePoint Online
    Read and write SharePoint Online data effortlessly. Integrate, manage, and automate sites, lists, document libraries, and files — almost no coding required.
    SharepointOnlineDSN
    Open Query Builder in API ODBC Driver to read and write data to REST API

  7. Start by selecting the Table or Endpoint you are interested in and then configure the parameters. This will generate a query that we will use in Power BI to retrieve data from SharePoint Online. Hit OK button to use this query in the next step. 

    SELECT * FROM Lists
    Configure table/endpoint parameters in ODBC data source based on API Driver
    Some parameters configured in this window will be passed to the SharePoint Online API, e.g. filtering parameters. It means that filtering will be done on the server side (instead of the client side), enabling you to get only the meaningful data much faster.

  8. Now hit Preview Data button to preview the data using the generated SQL query. If you are satisfied with the result, use this query in Power BI:

    ZappySys API Driver - SharePoint Online
    Read and write SharePoint Online data effortlessly. Integrate, manage, and automate sites, lists, document libraries, and files — almost no coding required.
    SharepointOnlineDSN
    SELECT * FROM Lists
    API ODBC Driver-based data source data preview
    You can also access data quickly from the tables dropdown by selecting <Select table>.
    A WHERE clause, LIMIT keyword will be performed on the client side, meaning that the whole result set will be retrieved from the SharePoint Online API first, and only then the filtering will be applied to the data. If possible, it is recommended to use parameters in Query Builder to filter the data on the server side (in SharePoint Online servers).

  9. Click OK to finish creating the data source.

Video Tutorial

Connect to SharePoint Online data in Power BI

Import data from a table or view

  1. Once you open Power BI Desktop click Get Data to get data from ODBC:

    Power Bi Get Data

  2. A window opens, and then search for "odbc" to get data from ODBC data source:

    Power Bi ODBC Get Data

  3. Another window opens and asks to select a Data Source we already created. Choose SharepointOnlineDSN and continue:

    SharepointOnlineDSN
    Power Bi Select ZappySys Driver DSN

  4. Most likely, you will be asked to authenticate to a newly created DSN. Just select Windows authentication option together with Use my current credentials option:

    SharepointOnlineDSN
    Power Bi DSN Authentication

  5. Finally, you will be asked to select a table or view to get data from. Select one and load the data!

    Power Bi Load DSN Table Data

  6. Finally, finally, read extracted data from SharePoint Online in a Power BI report:

    Power Bi Extracted DSN Table Data

Import data using a SQL query

If you wish to import SharePoint Online data from SQL query rather than a table then you can use advanced options during import steps (as below). After selecting DSN you can click on advanced options to see SQL Query editor.

SharepointOnlineDSN
SELECT * FROM Lists
Get REST API data in Power BI Desktop using SQL query and ODBC
Consider using Custom Objects feature in ODBC data source to encapsulate SQL query in a Virtual Table. This way, you can see a virtual table in Power BI table list where you can import multiple objects using the same connection rather than creating a new connection for each custom SQL query.

Publish Power BI report to Power BI service

Here are the instructions on how to publish a Power BI report to Power BI service from Power BI Desktop application:

  1. First of all, go to Power BI Desktop, open a Power BI report, and click Publish button:

    Publish Power BI report to Power BI service

  2. Then select the Workspace you want to publish report to and hit Select button:

    Publish Power BI report to workspace

  3. Finally, if everything went right, you will see a window indicating success:

    Successful Power BI report publishing

What's next? If you need to periodically refresh Power BI semantic model (dataset) to ensure data accuracy and up-to-dateness, you can accomplish that by using Microsoft On-premises data gateway. Proceed to the next section - Refresh the Power BI semantic model via the gateway - and learn how to do that.

Refresh the Power BI semantic model (dataset) via the gateway

Power BI allows you to refresh semantic models (previously known as "datasets") that are based on data sources residing on-premises. This is achieved using the Microsoft On-premises data gateway. It acts as a secure bridge between Power BI cloud services and your local SharePoint Online ODBC data source:

Power BI and On-premises data gateway diagram

There are two types of On-premises data gateways:

Standard mode
  • Supports Power BI and other Microsoft Cloud services
  • Installs as a Windows service
  • Starts automatically
  • Supports centralized user access control
  • Supports the Direct Query feature
  • Ideal for enterprise solutions
Personal mode
  • Supports Power BI services only
  • Cannot run as a Windows service
  • Stops when you sign out of Windows
  • Does not support access control
  • Does not support the Direct Query feature
  • Best for individual use and POC solutions

You can download the On-premises data gateway directly from the Microsoft Fabric or Power BI portals:

Download Power BI On-premises data gateway
We recommend using Personal mode for quick POCs or personal use, and Standard mode for production scenarios.

Below are instructions on how to refresh the semantic model using both gateway types.

Use the Standard mode gateway (recommended)

Best for enterprise production environments where multiple users need to share the same gateway connection.

Follow these steps to refresh a Power BI semantic model using the On-premises data gateway (Standard mode):

  1. Go to Power BI My workspace, hover your mouse cursor over your semantic model, and click Settings:

    Configure Power BI semantic model settings

  2. If you see this view, it means you must install the On-premises data gateway (Standard mode):

    On-premises data gateway is not installed

  3. Download On-premises data gateway (standard mode) and run the installer.

  4. Once the configuration window opens, sign in:

    Signing in to on-premises data gateway standard
    Sign in with the same email address you use for Microsoft Fabric.

  5. Select Register a new gateway on this computer (or migrate an existing one):

    Registering or migrating on-premises data gateway standard

  6. Name your gateway, enter a Recovery key, and click the Configure button:

    Naming on-premises data gateway standard
    Save your Recovery Key in a safe place (like a password manager). If you lose it, you cannot restore or migrate this gateway later.

  7. Once Microsoft gateway is installed, check if it registered correctly:

    • Go back to Power BI portal

    • Click Gear icon on top-right

    • And then hit Manage connections and gateways menu item

    Manage On-premise data gateways in Microsoft Fabric or Power BI

  8. Continue by clicking the On-premises data gateway tab and selecting Standard mode gateways from the dropdown menu:

    Access On-premises data gateway list (Standard mode) in Power BI

    If your gateway is not listed, the registration may have failed. To resolve this:

    • Wait a couple of minutes and refresh Power BI portal page
    • Restart the machine where On-premises data gateway is installed
    • Check firewall settings

  9. Success! The gateway is now Online and ready to handle requests.

  10. Now, return to your semantic model settings in the Power BI portal. Refresh the page, and you should see your newly created gateway. Click the arrow icon to expand the options, and then click the Add to gateway link:

    ODBC{"connectionstring":"dsn=SharepointOnlineDSN"}
    Using On-premises Data Gateway Standard for Power BI Semantic Model

  11. Once you do that, you will create a new gateway connection. Give it a name, set the Authentication method, Privacy level, and click the Create button:

    dsn=SharepointOnlineDSN
    Create new connection in Power BI On-premises data gateway
    In this example, we use the least restrictive Privacy level.

    If your connection uses a full connection string, you may hit a length limitation when entering it into the field. To create the connection, you will need to shorten it manually. Check the section about the limitation of a full connection string on how to accomplish this.

    On-premises data gateway (Personal mode) does not have this limitation.

  12. Select the newly created connection to map it to your dataset:

    ODBC{"connectionstring":"dsn=SharepointOnlineDSN"}
    Selecting gateway connection in Power BI semantic model

  13. Finally, you can refresh the semantic model:

    Refreshing Power BI semantic model using On-premises Data Gateway

Use the Personal mode gateway (POC)

Best for single-user scenarios, quick tests (POC), or when you don't have administrative rights to install the Standard gateway.

Follow these steps to refresh a Power BI semantic model using the On-premises data gateway (Personal mode):

  1. Go to Power BI My workspace, hover your mouse cursor over your semantic model, and click Settings:

    Configure Power BI semantic model settings

  2. If you see this view, it means you must install the On-premises data gateway (Personal mode):

    On-premises data gateway is not installed

  3. Install On-premises data gateway (personal mode) and sign-in:

    Sign-in to On-premises data gateway personal
    Use the same email address you use when logging in into your account.

  4. Once Microsoft gateway is installed, check if it registered correctly:

    • Go back to Power BI portal

    • Click Gear icon on top-right

    • And then hit Manage connections and gateways menu item

    Manage On-premise data gateways in Microsoft Fabric or Power BI

  5. Continue by clicking On-premises data gateway tab and select Personal mode option from the dropdown:

    Access On-premises data gateway list (Personal mode) in Power BI

    If your gateway is not listed, the registration may have failed. To resolve this:

    • Wait a couple of minutes and refresh Power BI portal page
    • Restart the machine where On-premises data gateway is installed
    • Check firewall settings

  6. The On-premises data gateway is now Online and ready to receive requests.

  7. Return to your semantic model Settings, expand Data source credentials, click Edit credentials, select the Authentication method and the Privacy level, and then click the Sign in button:

    dsn=SharepointOnlineDSN
    Use On-premises data gateway personal for Power BI semantic model

  8. Finally, you are ready to refresh your semantic model:

    Refreshing Power BI semantic model using On-premises Data Gateway

Advanced topics

Editing query in Power BI

There will be a time you need to change the initial query after importing data into Power BI. Don't worry, just right-click on your table and click Edit query menu item:

Edit query in Power BI to get REST API data
Refer to Power Query M reference for more information on how to use its advanced features in your queries.

Using parameters for dynamic queries

In the real world, many values of your REST / SOAP API call may be coming from parameters. If that's the case for you can try to edit script manually as below. In below example its calling SQL Query with POST method and passing some parameters. Notice below where paraAPIKey is Power BI Parameter (string type). You can use parameters anywhere in your script just like the normal variable.

To use a parameter in Power BI report, follow these simple steps:

  1. Firstly, you need to Edit query of your table (see previous section)

  2. Then just create a new parameter by clicking Manage Parameters dropdown, click New Parameter option, and use it in the query: 

                    
                            = Odbc.Query("dsn=SharepointOnlineDSN",
                                         "SELECT ProductID, ProductName, UnitPrice, UnitsInStock
                                          FROM Products
                                          WHERE UnitPrice > " & Text.From(MyParameter) & "
                                          ORDER BY UnitPrice")
    Use parameter in Power BI to get REST API data
    Refer to Power Query M reference for more information on how to use its advanced features in your queries.

Using DirectQuery (live connection)

By default, Power BI imports SharePoint Online data into its internal cache. However, if you require real-time data, you can use the DirectQuery mode.

Since the native Power BI ODBC connector limits you to Import mode, we must bridge the connection via Microsoft SQL Server. To do this, we configure the ZappySys Data Gateway and create a Linked Server pointing to it:

Architecture diagram: Power BI (DirectQuery) connected to SQL Server, bridging to ZappySys Data Gateway via Linked Server, and finally fetching data from SharePoint Online Service
This method requires an existing Microsoft SQL Server instance to act as the bridge.

Follow these steps to use DirectQuery:

  1. Configure ZappySys Data Gateway and create a Linked Server in SQL Server.

  2. Once the Linked Server is ready, open Power BI Desktop.

  3. Click Get Data and select SQL Server.

  4. Enter your SQL Server instance name and a valid database name (e.g., master).

  5. Under Data Connectivity mode, select DirectQuery.

  6. Expand Advanced options and enter your SQL query using the OPENQUERY syntax below (replace [LINKED_SERVER_NAME] with your actual Linked Server name):

    SELECT * FROM OPENQUERY([LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY], 'SELECT * FROM Lists')
    DirectQuery option for Power BI (read SharePoint Online data example using SQL Server Linked Server and ZappySys Data Gateway) 
    SELECT * FROM OPENQUERY([LS_TO_SHAREPOINT_ONLINE_IN_GATEWAY], 'SELECT * FROM Lists')
  7. Click OK and load the data. Your SharePoint Online data is now linked live rather than imported.
Avoid using DirectQuery unless it is required for very large datasets or real-time data needs. Data is fetched on demand, which can impact performance compared to the cached Import mode.

Using full ODBC connection string

In the previous steps we used a very short format of ODBC connection string - a DSN. Yet sometimes you don't want a dependency on an ODBC data source (and an extra step). In those times, you can define a full connection string and skip creating an ODBC data source entirely. Let's see below how to accomplish that in the below steps:

  1. Open ODBC data source configuration and click Copy settings:
    ZappySys API Driver - Configuration [Version: 2.0.1.10418]
    ZappySys API Driver - SharePoint Online
    Read and write SharePoint Online data effortlessly. Integrate, manage, and automate sites, lists, document libraries, and files — almost no coding required.
    SharepointOnlineDSN
    Copy connection string for ODBC application
  2. The window opens, telling us the connection string was successfully copied to the clipboard: Successful connection string copying for ODBC application
  3. Then just paste the connection string into your script:
    SharepointOnlineDSN
    DRIVER={ZappySys API Driver};ServiceUrl=https://graph.microsoft.com/v1.0;Provider=Custom;OAuthVersion=OAuth2;ScopeSeparator={space};GrantType=Default;Scope=[$Scope$];ScopeSeparator={space};ContentType=application/x-www-form-urlencoded;
    Use full connection string in Power BI Desktop to read API data
  4. You are good to go! The script will execute the same way as using a DSN.
The DSN defined in the Data source name (DSN) field will be ignored.

Have in mind that a full connection string has length limitations.

Proceed to the next step to find out the details.

Handling limitations of using a full connection string

Despite using a full ODBC connection string may be very convenient it comes with a limitation: it's length is limited to 1024 symbols (or sometimes more). It usually happens when API provider generates a very long Refresh Token when OAuth is at play. If you are using such a long ODBC connection string, you may get this error: 

"Connection string exceeds maximum allowed length of 1024"

But there is a solution to this by storing the full connection string in a file. Follow the steps below to achieve this:

  1. Open your ODBC data source.
  2. Click Copy settings button to copy a full connection string (see the previous section on how to accomplish that).
  3. Then create a new file, let's say, in C:\temp\odbc-connection-string.txt.
  4. Continue by pasting the copied connection string into a newly created file and save it.
  5. Finally, the last step! Just construct a shorter ODBC connection string using this format: 
    DRIVER={ZappySys API Driver};SettingsFile=C:\temp\odbc-connection-string.txt
  6. Our troubles are over! Now you should be able to use this connection string in Power BI with no problems.
This feature requires ODBC PowerPack v1.9.0 or later.

Optional: Centralized data access via ZappySys Data Gateway

In some situations, you may need to provide SharePoint Online data access to multiple users or services. Configuring the data source on a Data Gateway creates a single, centralized connection point for this purpose.

This configuration provides two primary advantages:

  • Centralized data access
    The data source is configured once on the gateway, eliminating the need to set it up individually on each user's machine or application. This significantly simplifies the management process.
  • Centralized access control
    Since all connections route through the gateway, access can be governed or revoked from a single location for all users.
Data Gateway
Local ODBC
data source
Simple configuration
Installation Single machine Per machine
Connectivity Local and remote Local only
Connections limit Limited by License Unlimited
Central data access
Central access control
More flexible cost

To achieve this, you must first create a data source in the Data Gateway (server-side) and then create an ODBC data source in Power BI (client-side) to connect to it.

Let's not wait and get going!

Create SharePoint Online data source in the gateway

In this section we will create a data source for SharePoint Online in the Data Gateway. Let's follow these steps to accomplish that:

  1. Search for gateway in the Windows Start Menu and open ZappySys Data Gateway Configuration:

    Open ZappySys Data Gateway Service Manager

  2. Go to the Users tab and follow these steps to add a Data Gateway user:

    • Click the Add button
    • In the Login field enter a username, e.g., john
    • Then enter a Password
    • Check the Is Administrator checkbox
    • Click OK to save
    Data Gateway - Add User

  3. Now we are ready to add a data source:

    • Click the Add button
    • Give the Data source a name (have it handy for later)
    • Then select Native - ZappySys API Driver
    • Finally, click OK
    SharepointOnlineDSN
    ZappySys API Driver
    Data Gateway - Add data source

  4. When the ZappySys API Driver configuration window opens, go back to ODBC Data Source Administrator where you already have the SharePoint Online ODBC data source created and configured, and follow these steps on how to Import data source configuration into the Gateway:

    • Open ODBC data source configuration and click Copy settings:
      ZappySys API Driver - Configuration [Version: 2.0.1.10418]
      ZappySys API Driver - SharePoint Online
      Read and write SharePoint Online data effortlessly. Integrate, manage, and automate sites, lists, document libraries, and files — almost no coding required.
      SharepointOnlineDSN
      Copy connection string for ODBC application
    • The window opens, telling us the connection string was successfully copied to the clipboard: Successful connection string copying for ODBC application

    • Then go to Data Gateway configuration and in data source configuration window click Load settings:

      SharepointOnlineDSN
      ZappySys API Driver - Configuration [Version: 2.0.1.10418]
      ZappySys API Driver - SharePoint Online
      Read and write SharePoint Online data effortlessly. Integrate, manage, and automate sites, lists, document libraries, and files — almost no coding required.
      SharepointOnlineDSN
      Load configuration in ZappySys Data Gateway data source
    • Once a window opens, just paste the settings by pressing CTRL+V or by clicking right mouse button and then Paste option.

  5. Once done, go to the Network Settings tab and Add a firewall rule for inbound traffic:

    Data Gateway - Add firewall rule for inbound connections
    • This will initially allow all inbound traffic.
    • Click Edit IP filters to restrict access to specific IP addresses or ranges.

  6. Crucial Step: After creating or modifying the data source, you must:

    • Click the Save button to persist your changes.
    • Hit Yes when prompted to restart the Data Gateway service.

    This ensures all changes are properly applied:

    ZappySys Data Gateway - Save Changes
    Skipping this step may cause the new settings to fail, preventing you from connecting to the data source.

Create ODBC data source to connect to the gateway

In this part we will create an ODBC data source to connect to the ZappySys Data Gateway from Power BI. To achieve that, let's perform these steps:

  1. Search for odbc and open the ODBC Data Sources (64-bit):

    Open ODBC Data Source

  2. Create a User data source (User DSN) based on the ODBC Driver 17 for SQL Server driver:

    ODBC Driver 17 for SQL Server
    Create new User DSN for ODBC Driver 17 for SQL Server
    If you don't see the ODBC Driver 17 for SQL Server driver in the list, choose a similar version.

  3. Then set a Name for the data source (e.g. Gateway) and the address of the Data Gateway:

    ZappySysGatewayDSN
    localhost,5000
    ODBC driver for SQL Server - Setting hostname and port
    Make sure you separate the hostname and port with a comma, e.g. localhost,5000.

  4. Proceed with the authentication part:

    • Select SQL Server authentication
    • In the Login ID field enter the user name you created in the Data Gateway, e.g., john
    • Set Password to the one you configured in the Data Gateway
    ODBC driver for SQL Server - Selecting SQL Authentication

  5. Then set the default database property to SharepointOnlineDSN (the one we used in the Data Gateway):

    SharepointOnlineDSN
    SharepointOnlineDSN
    ODBC driver for SQL Server - Selecting database
    Make sure to type the data source name manually or copy/paste it directly into the field. Using the dropdown might fail because the Trust server certificate option is not enabled yet (next step).

  6. Continue by checking the Trust server certificate option:

    ODBC driver for SQL Server - Trusting certificate

  7. Once you do that, test the connection:

    ODBC driver for SQL Server - Testing connection

  8. If the connection is successful, everything is good:

    ODBC driver for SQL Server - Testing connection succeeded

  9. Done!

We are ready to move to the final step. Let's do it!

Access data in Power BI via the gateway

Finally, we are ready to read data from SharePoint Online in Power BI via the Data Gateway. Follow these final steps:

  1. Go back to Power BI.

  2. Once you open Power BI Desktop click Get Data to get data from ODBC:

    Power Bi Get Data

  3. A window opens, and then search for "odbc" to get data from ODBC data source:

    Power Bi ODBC Get Data

  4. Another window opens and asks to select a Data Source we already created. Choose ZappySysGatewayDSN and continue:

    ZappySysGatewayDSN
    Power Bi Select ZappySys Driver DSN

  5. When the ODBC driver authentication window opens, configure the following:

    • Enter the User name (e.g., john)
    • Enter the Password that you configured in ZappySys Data Gateway
    • Hit the Connect button
    dsn=ZappySysGatewayDSN
    Configure access to ZappySys Data Gateway data source in Power BI
    Make sure the Database tab is selected; otherwise, Power BI won't be able to connect to the ZappySys Data Gateway.

  6. Read the data the same way we discussed at the beginning of this article.

  7. That's it!

Now you can connect to SharePoint Online data in Power BI via the Data Gateway.

Supported SharePoint Online Connector actions

Got a specific use case in mind? We've mapped out exactly how to perform a variety of essential SharePoint Online operations directly in Power BI, so you don't have to figure out the setup from scratch. Check out the step-by-step guides below:

Conclusion

In this article we showed you how to connect to SharePoint Online in Power BI and integrate data without writing complex code — all of this was powered by SharePoint Online ODBC Driver.

Download ODBC PowerPack now or ping us via chat if you have any questions or are looking for a specific feature (you can also reach out to us by submitting a ticket):

Download Chat

Explore Power BI connectors

All
Big Data & NoSQL
Database
CRM & ERP
Marketing
Collaboration
Cloud Storage
Reporting
Commerce
API & Files

More SharePoint Online integrations

All
Data Integration
Database
BI & Reporting
Productivity
Programming Languages
Automation & Scripting
ODBC applications