connecting notion database

[Anonymous]

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

[ImkeF · Accepted solution]

Hi @Anonymous ,
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.

[Oleksandr_2023o]

Hi everyone, 

I have the same problem as haf_
I could not update the dashboard in Power BI Server due to this mistake: Web.Contents failed to get contents from 'https://api.notion.com/v1/databases/69c79f44c0af4fb2ac6fa9c140b5ff58/query' (400): Bad Request

Do you know how to manage this mistake? Thank you in advance) 

[ImkeF · Accepted solution]

Hi @Anonymous ,

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)

[farroo]

Hello @ImkeF 
This is a great guide, I came here to say Thanks!
It has got me somewhere using your code, I rather have another basic thing missing.
I can get all my records but expanding them opens up 5 more fields in all record and there are few record fields, Is there any way to extract them all in one go?
Thanks!
--

let
Source = Json.Document(
Web.Contents(
"https://api.notion.com/v1/databases/0fc54*/query",
[
Headers = [
Authorization = "Bearer*",
#"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/*f852d52252/query",
[
Headers = [
Authorization = "Bearer *",
#"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"),
#"Expanded results1" = Table.ExpandRecordColumn(#"Expanded results", "results", {"properties"}, {"results.properties"})
in
#"Expanded results1"

[haf_]

Hi ImkeF, thank you for the code. It works perfectly in Power BI Desktop. Though in the PBI Service I am getting the "Failed to update data source credentials" error.

Do you have any idea how to refresh the data online?

Failed to update data source credentials: Web.Contents failed to get contents from 'https://api.notion.com/v1/databases/4db1bc42bf2b4e1a81b6c7056b41321d/query' (400): Bad RequestHide details

Activity ID:	c510cc15-88ac-4552-80d9-c73811b2c14c
Request ID:	209bd64e-080e-81b4-9413-e484347b1fda
Status code:	400
Time:	Sat Mar 04 2023 12:54:38 GMT+0100 (Central European Standard Time)
Service version:	13.0.20148.59
Client version:	2302.3.12445-train
Cluster URI:	https://wabi-north-europe-l-primary-redirect.analysis.windows.net/

[Anonymous]

Many thanks as always, @ImkeF.

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

Kind regards, -Nik

[ImkeF · Accepted solution]

Hi @Anonymous ,
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.

[Anonymous]

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

[ImkeF]

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:

[Anonymous]

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.

[ImkeF]

Hi @Anonymous ,
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/<YourNotionDatabaseID>,
[Headers = [Authorization= "Bearer <YourNotionToken>, #"Notion-Version"= "2022-02-22"]] )

[Anonymous]

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/<NotionDatabaseID>/query), I managed to generate a JSON file with all the values in there.