Notion and Power BI integration with Rest in a Power BI Data Connector

Power BI Notion Integration with rest

In this blog post you will learn :
- How to do rest post query to Notion with Power BI.
- How to do pagination with cursor using Power BI.

My colleagues from product team chose Notion to manage their activities ( product strategy, discovery, roadmap management etc…)
Data beats opinion and we do BI using Power BI 📈 with product and tech data to follow our cherished product the Younited Credit platform.

Our need is to get all notion database rows at once.

Power bi services do not support necessary logic for header access token authentication. All right it is Microsoft choice. Its not a good practice to hardcode acces token in power m script. This is why the snippets above should be packaged in a data connector then credentials are stored in a secured space outside of script. In order to use the data connector with power bi services you will need to deploy it in a on-premises data gateway.

Long story short Four Power BI functions are necessary to integrate Power BI and Notion.

Do rest post query to Notion with Power BI

First function is doing the Post Rest Request and call database query Notion feature to get items in a Json Respons.
The query Notion feature provide paginated results with max page size set to 100 rows.
Using cursor let you get a page from the cursor to next 99 rows.
As Notion is mostly base on data bases the function take data base id as parameter. It will let you query any base of your integration.

Access token should be the integration token provided by Notion

fGetNotionItems=(cursor as text, databaseId as text) =>
  let
    accesstoken = Extension.CurrentCredential()[Key], 
    headers = [#"Authorization" = accesstoken , #"Notion-Version"="2022-06-28" , #"Content-Type" = "application/json"],
    postData = Json.FromValue(
        [
            page_size = 100,
            start_cursor = cursor,
            sorts =Table.FromRecords({[timestamp="created_time", direction = "ascending"]})
        ]
        ), 
    response =  Web.Contents(   "https://api.notion.com",
        [   
            RelativePath="v1/databases/"&databaseId&"/query",
            Headers = headers,
            Content =postData
        ]
        ),
        jsonResponse = Json.Document(response)
  in
    jsonResponse;

In order to be supported by power bi online Web.Content Relative path is used to build the url instead of basic string concatenation leading to the following error message This dataset includes a dynamic data source.

The second function get the First Cursor is very closed to the previous

fGetNotionFisrtCursor = (databaseId as text)=>
let
    accesstoken = Extension.CurrentCredential()[Key],
    headers = [#"Authorization" = accesstoken , #"Notion-Version"="2022-06-28" , #"Content-Type" = "application/json"],
    postData = Json.FromValue(
        [
            page_size = 1,
            sorts =Table.FromRecords({[timestamp="created_time", direction = "ascending"]})
        ]
        ), 
    response =  Web.Contents(
        "https://api.notion.com",
        [   
            RelativePath="v1/databases/"&databaseId&"/query",
            Headers = headers,
            Content =postData
        ]
        ),
        jsonResponse = Json.Document(response),
        fisrtCursor =  jsonResponse[results]{0}[id]
in fisrtCursor;

Pagination with cursor using Power BI

The Third Function iterates to get all rows from a database.
This function uses List.Generate() Power Query M function and iterates using next_cursor as described in Notion documentation.

fNotionSearchPaginate=(cursor as text, databaseId as text) =>
  let
    QueryNotion = List.Generate(
      () => [Result = try fGetNotionItems(cursor, databaseId) otherwise null, Cursor = "cursor"], 
      each [Result] <> null, 
      each [
        Result = try
          fGetNotionItems(Record.Field([Result], "next_cursor"), databaseId)
        otherwise
          null, 
        Cursor = Record.Field([Result], "next_cursor")
      ], 
      each [Result]
    )
  in
    QueryNotion

There is a good tutorial on Youtube to understand List.Generate() and pagination with a simple example if your are not familiar with this feature.

Getting database

Finally our needs is supported and you just need to call Notion.DB( database id )

[DataSource.Kind="Notion", Publish="Notion.Publish"]
shared Notion.DB = (DatabaseID as text) =>
    let
        cursor=fGetNotionFisrtCursor(DatabaseID),
        database = fNotionSearchPaginate(cursor ,DatabaseID ),
        apiKey = Extension.CurrentCredential()[Key]
    in
        database;

// Data Source Kind description
Notion = [
    Authentication = [
         Key = []
        // UsernamePassword = [],
        // Windows = [],
        //Anonymous = []
    ]
];

Enjoy Notion BI 🙂