How to read Notion database table in PowerBI using ODBC

Contents hide

1 Introduction

2 Prerequisites

3 Steps

3.1 Create a Notion Integration and Token

3.2 Connect Your Notion Database to the Integration

3.3 Creating a new ODBC DSN using ZappySys JSON Driver

3.4 Configure the Connection and API Settings

3.5 Preview Notion table data in the ODBC Driver

3.6 Connect Power BI to Notion via ODBC

3.7 Query Notion Data with SQL

4 Conclusion

5 Need Help?

Introduction

Notion is a versatile workspace platform that many teams use to manage projects, tasks, documentation, and databases. However, to leverage this data for advanced analytics and business intelligence, you need to connect it to tools like Power BI. Using Zappysys ODBC Driver for Notion, you can seamlessly extract data from Notion databases and visualize it in Power BI without writing any code.

This tutorial demonstrates how to set up a connection from Notion to Power BI using the Zappysys ODBC driver, query your Notion databases, and create interactive Power BI dashboards.

Prerequisites

A Notion workspace with at least one database and records.
Notion API token (Personal Access Token or Integration token).
Power BI Desktop is installed on your system.
Basic understanding of Power BI and SQL queries.
Finally, install the ZappySys ODBC Driver.

Steps

Create a Notion Integration and Token

Go to Notion Integrations and sign in.
Go to the My Integrations section and click New Integration.
Enter a name for your integration and select the target workspace.
Click Create connection to proceed.
Notion – Create a new connection
Configure the capabilities you need by checking or unchecking the relevant options.
Copy the generated Access Token and store it securely. You’ll need this token to configure the ODBC driver.
Notion – Configure new token

Connect Your Notion Database to the Integration

Open your target Notion database page in the browser.
Click … in the top-right corner.
Click Connections, search for your integration, and select it.
Confirm access to the connection.
Copy the database ID from the URL. For example:
https://app.notion.com/p/<DATABASE_ID>?v=......................
Notion – Set a connection to your table

Creating a new ODBC DSN using ZappySys JSON Driver

Search for ODBC in the Windows Start menu and open the ODBC Data Source Administrator.
In the User DSN or System DSN tab, click Add to create a new data source.
In the ODBC Data Source Setup window, select the ZappySys JSON Driver and click Continue.

Configure the Connection and API Settings

Enter the base URL for the table:
https://api.notion.com/v1/databases/{{User::DatabaseId}}/query
In Connection Type, select HTTP and click Configure.
Select Static Token/API Key as the Credential type.
Paste your Notion Access Token in the authentication field.
Click OK to save the configuration.
Notion – ODBC Connection configuration
Set HTTP Request Method to POST.
Set Body content type to JSON (application/json).
Under headers, add: Notion-Version: {NotionVersion}. (Example: 2022-06-28)
Set JSON Path Filter to extract the object array. Example: $.results[*]
Click Test Connection to verify connectivity, then click OK to save the DSN
Notion – ODBC JSON driver configuration

Preview Notion table data in the ODBC Driver

Go to the Preview tab.
Select a table, generate a query, and then click Preview Data to confirm that database records are returned.
Notion – ODBC JSON driver result

Connect Power BI to Notion via ODBC

Open Power BI Desktop.
Click Get Data in the Home ribbon.
Click More… and search for ODBC.
Click Connect.

Power BI – Get data from ODBC connection
From the dropdown, select the Notion ODBC data source you created earlier.
You can enter a custom query that you used in the ODBC JSON driver.
Click OK.
Notion – Power BI connection
Click Load to import the data into Power BI.
In the Navigator window, select the Notion columns you want to load.
Notion – Power BI final result

Query Notion Data with SQL

For more advanced scenarios, you can write custom SQL queries to extract specific data from your Notion database. Here are some common query examples:

Example 1: Extract All Database Records

SELECT 
    Id,
    Title,
    Status,
    CreatedTime,
    LastEditedTime
FROM Databases
WHERE DatabaseId = 'YOUR_DATABASE_ID'

SELECT

Id,

Title,

Status,

CreatedTime,

LastEditedTime

FROM Databases

WHERE DatabaseId = 'YOUR_DATABASE_ID'

Example 2: Filter Records by Status

SELECT 
    Id,
    Title,
    Status,
    Priority,
    DueDate
FROM Databases
WHERE DatabaseId = 'YOUR_DATABASE_ID'
  AND Status = 'In Progress'

SELECT

Id,

Title,

Status,

Priority,

DueDate

FROM Databases

WHERE DatabaseId = 'YOUR_DATABASE_ID'

AND Status = 'In Progress'

Example 3: Count Records by Status

SELECT 
    Status,
    COUNT(*) AS RecordCount
FROM Databases
WHERE DatabaseId = 'YOUR_DATABASE_ID'
GROUP BY Status

SELECT

Status,

COUNT(*) AS RecordCount

FROM Databases

WHERE DatabaseId = 'YOUR_DATABASE_ID'

GROUP BY Status

Example 4: Get Recent Records (Last 30 Days)

SELECT 
    Id,
    Title,
    Status,
    CreatedTime
FROM Databases
WHERE DatabaseId = 'YOUR_DATABASE_ID'
  AND CreatedTime >= DATEADD(DAY, -30, CAST(GETDATE() AS DATE))

SELECT

Id,

Title,

Status,

CreatedTime

FROM Databases

WHERE DatabaseId = 'YOUR_DATABASE_ID'

AND CreatedTime >= DATEADD(DAY, -30, CAST(GETDATE() AS DATE))

Example 5: Join Multiple Database Properties

SELECT 
    d.Id,
    d.Title,
    d.AssignedTo,
    d.Status,
    d.DueDate
FROM Databases d
WHERE d.DatabaseId = 'YOUR_DATABASE_ID'
  AND d.AssignedTo IS NOT NULL

SELECT

d.Id,

d.Title,

d.AssignedTo,

d.Status,

d.DueDate

FROM Databases d

WHERE d.DatabaseId = 'YOUR_DATABASE_ID'

AND d.AssignedTo IS NOT NULL

Example 6: Aggregate Data by Multiple Dimensions

SELECT 
    Status,
    Priority,
    COUNT(*) AS TaskCount,
    AVG(CAST(Duration AS FLOAT)) AS AvgDuration
FROM Databases
WHERE DatabaseId = 'YOUR_DATABASE_ID'
GROUP BY Status, Priority

SELECT

Status,

Priority,

COUNT(*) AS TaskCount,

AVG(CAST(Duration AS FLOAT)) AS AvgDuration

FROM Databases

WHERE DatabaseId = 'YOUR_DATABASE_ID'

GROUP BY Status, Priority

Conclusion

Connecting Notion to Power BI using the Zappysys ODBC Driver provides a seamless way to bring your workspace data into a powerful analytics platform. With SQL queries, DAX measures, and visualisations, you can unlock insights from your Notion databases and create compelling business intelligence reports.

Explore the full capabilities of the Zappysys ODBC Drivers to integrate multiple data sources and build comprehensive Power BI dashboards. By combining Notion data with other sources, you can create unified views of your operational data.

Need Help?

If you encounter any issues, our support team is here to help:

Live Chat — Use the chat widget (bottom-right corner of this page)
Email — support@zappysys.com
Support Center — Visit the ZappySys Support Portal