connecting notion database

[Syndicate_Admin]

Hi,
I'm trying to find a REST API solution to allow Notion database connection to MS Power BI via Power Query.

So far, I'm only able to generate JSON file from a Notion dbase that I import in MS Power BI via Power Query for ETL. I'd like to have direct connection to Notion dbase via Get Data Web function in MS Power BI / Power Query where I am still not successful.

Many thanks in advance.
Kind regards, -Nik

[Syndicate_Admin]

Hi @nikahafiz ,
to start, you have to create an integration in Notion: Getting started (notion.com)

This will give you the authentication token.
Make sure to share content with that integration, as that's a prerequisite to receive anything.
All this is described in detail the link above.
Then a basic call for an internal app with simple authentication would look like so:

Web.Contents( "https://api.notion.com/v1/databases/,
[Headers = [Authorization= "Bearer , #"Notion-Version"= "2022-02-22"]] )

[Syndicate_Admin]

Thanks for your quick reply, @ImkeF.

I have actually managed use the basic call as u provided earlier. when opening the 'property' object, I managed to get the following:

However, only the Mood's Value can be expanded to show the items in there. In trying to expand the Email and Name's Value, I didn't manage ti get any content from them. Is there anything that is amissed in Notion or Power Query. 

KRgds, -Nik
p.s.
To kindly recap, when I did a POST query in POSTMAN (https://api.notion.com/v1/databases//query), I managed to generate a JSON file with all the values in there.

[Syndicate_Admin]

Hi Nik,
it would have been very nice if you would have given this information in the original thread already, as it would have saved me some time.
If you are using the same URL in PQ and in Postman there is a good chance that the same data is returned.
But as you're not giving your M-code here, I can only assume it at this time.
Your problem is probably that you're not expanding the record column correctly:
When clicking on the double-arrows, you will see the record fields from the first record. If the other records have different fields, you can expand them by clicking on the blue "Load more"-button:

[Syndicate_Admin]

many thanks again, @ImkeF.

since this is just a test dbase, here's the m-code:

let
Source = Json.Document(Web.Contents("https://api.notion.com/v1/databases/4db1bc42bf2b4e1a81b6c7056b41321d", [Headers=[Authorization="secret_hc4P70yKhZeK8xbzTsAaLgLeTUYPBmXZ6zDsH09bia5", #"Notion-Version"="2022-02-22"]])),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{9}[Value],
#"Converted to Table1" = Record.ToTable(Value)
in
#"Converted to Table1" 

[ the notion db is also shared publicly via https://local-salt-8bd.notion.site/4db1bc42bf2b4e1a81b6c7056b41321d?v=9b0ea105aa2f40c1b0f5fe6a8e904b... ]

in the m-code, i didn't expand the record column fully as i know i'm still getting no details for email & name. the result will be as per the screenshot image in my earlier email 

i'd appreciate any guidance for me to be able to retrieve the notion records in ms power bi / power query. 

thanks & kind regards, -nik

p.s.

actually i have mentioned in my 1st email tht i have been able to create/download a json file for this through postman. however, now i wish to get the same directly via power query instead of a downloaded json file, if possible. i'm sorry for any miscommunication/confusion.

[Syndicate_Admin]

Hi @nikahafiz ,
the devil lies in the detail here: When I use the GET request from my PQ query in Postman I get the same result than in PQ: Empty email and name.
However, I missed that you were using a POST-request instead. You can convert the PQ GET request to a post request by adding a content parameter to the record like so. I'm using page_size = 100 in the body here:

let
Source = Json.Document(Web.Contents("https://api.notion.com/v1/databases/4db1bc42bf2b4e1a81b6c7056b41321d/query", [Headers=[Authorization="secret_hc4P70yKhZeK8xbzTsAaLgLeTUYPBmXZ6zDsH09bia5", #"Notion-Version"="2022-02-22"], Content = Json.FromValue([page_size=100])])),
    results = Source[results],
    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"object", "id", "created_time", "last_edited_time", "created_by", "last_edited_by", "cover", "icon", "parent", "archived", "properties", "url"}, {"object", "id", "created_time", "last_edited_time", "created_by", "last_edited_by", "cover", "icon", "parent", "archived", "properties", "url"}),
    #"Expanded properties" = Table.ExpandRecordColumn(#"Expanded Column1", "properties", {"Mood", "Email", "Name"}, {"Mood", "Email", "Name"}),
    #"Expanded Email" = Table.ExpandRecordColumn(#"Expanded properties", "Email", {"id", "type", "email"}, {"id.1", "type", "email.1"}),
    #"Expanded Name" = Table.ExpandRecordColumn(#"Expanded Email", "Name", {"id", "type", "title"}, {"id.2", "type.1", "title"})
in
    #"Expanded Name"

The reason for the different result is probably that email and name are complex fields, as they are separate database objects themselves.

[Syndicate_Admin]

Many thanks again, @ImkeF.

It workds fine for me too (also after further transformation).

I know I'm asking abit more but can you kindly advise if I can show more than 100 records if they dynamically change?

Kind regards, -Nik

[Syndicate_Admin]

Thanks again, @ImkeF.

If I may ask abit more, do you know how to get help on Botion's pagination API (to get >100 records per request)?

Kind regards, -Nik

[Syndicate_Admin]

Hi @nikahafiz ,

sure. This is an example where the next cursor info has to be passed in the body:

let
    Source = Json.Document(
        Web.Contents(
            "https://api.notion.com/v1/databases/4db1bc42bf2b4e1a81b6c7056b41321d/query",
            [
                Headers = [
                    Authorization     = "secret_hc4P70yKhZeK8xbzTsAaLgLeTUYPBmXZ6zDsH09bia5",
                    #"Notion-Version" = "2022-02-22",
                    #"Content-Type"   = "application/json"
                ],
                Content = Json.FromValue([page_size = 20])
            ]
        )
    ),
    Custom1 = List.Generate(
        () => [Result = Source, prevHasMore = true],
        each [prevHasMore] = true,
        each [
            prevHasMore = [Result][has_more],
            Result = Json.Document(
                Web.Contents(
                    "https://api.notion.com/v1/databases/4db1bc42bf2b4e1a81b6c7056b41321d/query",
                    [
                        Headers = [
                            Authorization     = "secret_hc4P70yKhZeK8xbzTsAaLgLeTUYPBmXZ6zDsH09bia5",
                            #"Notion-Version" = "2022-02-22",
                            #"Content-Type"   = "application/json"
                        ],
                        Content = Json.FromValue(
                            [page_size = 20, start_cursor = [Result][next_cursor]]
                        )
                    ]
                )
            )
        ]
    ),
    #"Converted to Table" = Table.FromList(
        Custom1,
        Splitter.SplitByNothing(),
        null,
        null,
        ExtraValues.Error
    ),
    #"Expanded Column1" = Table.ExpandRecordColumn(
        #"Converted to Table",
        "Column1",
        {"Result"},
        {"Column1.Result"}
    ),
    #"Expanded Column1.Result" = Table.ExpandRecordColumn(
        #"Expanded Column1",
        "Column1.Result",
        {"object", "results", "next_cursor", "has_more", "type", "page"},
        {"object", "results", "next_cursor", "has_more", "type", "page"}
    ),
    #"Expanded results" = Table.ExpandListColumn(#"Expanded Column1.Result", "results")
in
    #"Expanded results"

With a page size of 20 and 110 total rows, it runs 6 times to get the total result.
Please note that I've had to add a Content-Type parameter to the header.
If you're wondering about the "prevHasMore"-field, you can read more about it here: How not to miss the last page when paging with Power BI and Power Query (thebiccountant.com)

[Syndicate_Admin]

Many thanks as always, @ImkeF.

You have been humbly helpful many times here & elsewhere too.

Kind regards, -Nik