Unleash the Power: Connect Notion to PowerBI or Excel

hi there guys Lor is back thank you so much for watching um today is going to be a special one I will be connecting power query to notion and we'll be getting some um data from notion databases there are quite some steps involved in this process so make sure to watch till the end um but yeah without further Ado let's let's let's get going all right so I'm in my notion I've created a special workplace just for you guys called YouTube testing space and and I have a um a special page called demo support tickets here uh demo support page and I have a list of 30 fake um support tickets let's say I want to um show some kind of a chart in in powerbi or Excel or I just want to Crunch this data um and have it always refreshing through Power query now how how would I do that now as you may or may not know notion has a pretty nice API and you can get the the API reference by going to app um no what what's called notion API um you can just type notion API um to Google click on the first link and then here you have this um I usually just go to this API reference jump to the reference um and here it is so today we'll be taking a look at this um endpoint section um specifically this database section and we'll be using this query a database um endpoint so if I go here I can check out the URL so we'll be using this this URL to query and as you can see the only parameter that we need is this database ID and we'll be I'll be showing you how to retrieve that so um now there are as I said there there are some steps to get there um before being able to use this Endo we need an API key how to get an API key you can go to settings and members and you can go to connections um and develop or manage Integrations click on here and now as you can see I don't have any integration at the moment I can click on new integration and I can um okay that's all fine Associated workspace make sure to select the the right one so I'll be using this YouTube uh YouTube testing space and the name for this integration will be powerbi powerbi okay that's all fine and I'll click on submit and now that I have it um I can click on show and this is my API secret um yeah okay so I can copy that and um just paste it in Excel okay I've got my Excel Window open I can go to data get data um launch power query editor um here it is as usual I can just uh first thing what I'll do is I will create a new parameter and I'll will click on manage parameters new parameter and I will type in API key um so just just so that I can store this API key in one place um as a variable so if it changes uh which it will just after this video you you've thought that I won't change this and maybe I'll forgot and you you'll be able to hack my notion anyway uh so type I will set it to I will set it to um text and the current value will be the no that's not the one let me go back to my um to my my integration and let me just copy this and now I can paste in this value all right so the API should be fine now and I can click on okay all right so the API key is here now I can go back to my notion to my notion page and um yeah that's all fine now I need um yeah the next thing that I need to do is add this integration share this database with my integration so to do that um I can click here on the database which I want to which I want to share um with with integration this is the support tickets and I can go to um here at the top click on three three um three dots add connections and then type in the name of my integration let's say powerp um let me try to refresh the page first I don't see it straight away let me try that again add connections um Power bi here I can see it on on the list and I just realized you couldn't see that before so here's the add connections I just typed in powerbi in in into search and I can click on powerbi and now I need to confirm that I want to add um this integration to this page I'll click on confirm and now um it it received this this little Green Dot which means that um and it said says can read content can read can insert content can update content and that's what's called capabilities if I go back to my Integrations page um you can see that there's this secret page Secrets page and there's also this capabilities page so you can enable additional capabilities um for example or or remove capabilities so if you're only using this for powerbi then I strongly suggest that you just remove update content and insert content um just just to make sure that nobody does a stupid thing when they F find your API key um and you can also enable read comments if if that's what you need uh so yeah here is here here's how how to enable that uh and there are some user capabilities but we won't be going into that today so I can just click on Save changes and that's it so um we've taken look at these capabilities and as you can see um it wow that was quick so cannot insert content cannot update cannot comment cannot read comments it can just read content this is exactly what we need I did not need to refresh the page it just worked so that's nice all right um so the last thing we need to do is get the get the um database ID and you can get that by um opening your notion on the web or if you if you um yeah if you don't know let me just show you how to do it the other way so what you can do is click here on these three dots here um under the table and you can do copy link to view and as you can see link to view copy to clipboard and now what I will do is I'll just um paste that into my Chrome and I will create a screenshot so you can see more clearly what's going on so this is the kind of URL that you'll be you'll be getting so um htps notion.so and this thing this first from F until this uh question mark this is the database ID this is what you need to copy and this is what you'll need in Excel uh in power query when getting the data so I'll just close this screenshot and copy my um database ID okay that's great now I can go back to my Excel and create a new parameter manage parameters uh new parameter the name will be database ID uh okay I just I'm just making sure that I don't make any typos and typo will be text current value will be this one all right so we have the API key which is a parameter we have the database database ID which is also a parameter so now we're all set um to create a new query and finally get the data so I can click on new query um blank query and I will just click here on um let me just let me just remove my face so it'll be easier if if I go to I can go to next um all right so I have my query and I can click on shift command and M to open the advanced editor okay perfect now I'm in so the first thing we will need to do is construct the final URL so I will just do DB URL equals and now I'll go back to notion documentation um to to get this URL so this is the end point that we'll be using so https and then this AP api. notion.com and this database ID so let me just copy that go back into Excel um and paste this URL in and now I will replace this database ID uh with the actual database ID and of course I'll be using ents to to um add the strings together and I can type database ID I believe it's it's uppercase let me check that later um okay so DB URL that's all fine let me see if if that went through correctly I can just click on okay okay looks like this worked so this is the final URL that will be calling perfect okay let me go back to my power query editor let me create create a new variable and this one will be um notion request notion request equals um web. contents and of course this is the function that that executes the um that executes the actual HTTP request and I can also um just quickly show you the documentation for this so documentation is available on Microsoft of course let me just um show you the URL so this is the URL learn. microsoft.com powerquery web contents um and it you can actually just search for web. contents on Google and you'll see um you'll see this exact document and we'll be using this specific example here because it has this nice json. from value now one thing to note here is that web. contents by default sends um get request get requests so um and there's there's a little comment saying specifying so there's this content parameter and it's it's described as specifying this value changes the web request from get to post so if I go back to my notion um to my notion documentation uh quer a database and uh as you can see here this is a post request it's not a get request it's a post request so we will be providing an empty Json object here just to fulfill um just Ju Just to make powerbi happy um so let's get started all right um let me just copy this this example I think it'll be it'll be easier um yes exactly I need everything from here I'll just copy that go back to Power query so web. contents let me just paste that in um okay web. Contents I will be just um let let me just use the what what I prepared already but I'll be following this example so URL is R DB URL next parameter is a as you can see it's it's an object uh so I can copy that um and then we need the headers and we can actually let me just create this um request in in one variable instead of creating multiple um so this will be an OP object and I'll go to notion documentation right after I paste this so let me go back to notion documentation quickly so um uh let me zoom out a bit so here's an example request as as you can see um it has it has this URL this is correct we already did that then they use this authorization header which will also be using and they need this bare uh prefix as as usual so bar authentication and they also need two additional header header parameters one of them is notion version and the other one is content type which I've already pasted into um into the uh the power query so I can go ahead and just copy and paste this notion version as well go back to Power query so I will create another um another header uh header value and um same same as here if you if if the header if variable Keys If Fields contain weird characters like like minus here um you need to enter you need to add a hash hashtag followed by quotes and now I can just paste notion version and this is equal to another string and let me just copy and paste what's here um this might be different when you're watching this because notion is updating things um well not so not so often as as as you can see here but anyway this might be different so just go to notion documentation and copy paste this value um and the third thing we need is authorization as as I said before uh authorization this one does not need this hashtag and quotes because it's just a simple name so I can go ahead and type in bar um space and then concatenate that and I will use API key here uh as you as you probably remember API key is our parameter which holds our notion API key so we'll be using that here now apart from headers we also need to specify content so I will do content equals and it says here post data I'm still copying that from my example and post data equals ALS to json. from value and uh so what this does is it converts power query um record into Json string this is essentially exactly what we need uh but we need an empty object here so it just need to be an object um we we need no filters here we need no sorting you can read more on on um notion documentation how to how to do sorting if you want to do it on the source um but if you just want to get all records then the empty um record is fine so this is pretty much it then I can just remove this the the remaining of the example but I can keep this Json response so um there this this is another value this is another variable and it just converts everything that web contents retrieves it converts it to um Power query records and or tables so I will use this notion request here instead of response and let me return this Json request uh J response and click on okay let's see what errors we receive okay so no errors this time um this is strange but it's true that I tested it um straight before before starting this video so you might get a notification about um about credentials not being specified or something like that so um you can just click on manage manage credentials actually let me let me try to fix that um so I can go to I can go to home and click on close a load um and go to data source settings so I went back to to Excel and there's this there's this table and there's this data um Tab and you you can go to this get data power query just click on this drop down click on data source settings and as you can see I have this exception here or permission settings already set so I can click on clear permissions click on delete and click on close why I'm show showing this to you uh because you might have issues with with authentication somewhere so if you need to to delete all all the cache or yeah just all all credentials you've previously entered just use this trick to to remove everything that was previously entered so you can start fresh so now I can go back to my power query editor and as you can see we have an error now and yeah this is exactly the error I was trying to get so if you get this error just click on configure connection uh select Anonymous and click connect and this should then make Power query happy one other thing that you might face is a weird mess error message saying that you need an on premises data gateway gateway or if you're on Windows there is um yeah you'll get something related to privacy so to get rid of that just go to home click on options and search for privacy in powerbi it's a bit different but it still has to do with privacy so um click here on allow combining data from multiple sources just click on that and click on okay um yeah all right so now with that done this is actually the data we we retrieved um now this this this is a simple example because there I have only 30 records um there might be additional complications if there were more than I'm not really sure what's the API limit for that but um if there is um um pagination so if if there are more than x records I'm not sure what's the actual number number um you might need to to do multiple requests and there might be some issues with that if you want to see how that's done um just drop me a comment I'm going to show you that as well in the next video um but today what we can do is we can just crunch this data that we retrieved so we have this results key and it contains a list and what we want to do is just transform this list into a table so power query um will know what to do with it uh right now it really doesn't so we can go back to our query and I have the Json response and I can do data equals to um and this will be table from list I believe from list and of course I don't get the functions right the first time so there's a high chance this will not work and this is how to how to um get the the the key um and now click on okay now um all right yeah of course I forgot to specify the return data and of course uh the field result the record wasn't found okay let's let's see what went wrong I can go one step back here so it's actually results this is this is the issue I'm getting so results s all right uh go back to to this one data we cannot convert the value of type record to type text um interesting let me let me actually let's do it let's do it the other way around um let's not be too smart this time so I can just remove this uh data variable here uh like this by clicking on this X and I can just click on on the list here voila and I get this list of records and now what I can do is just click on this two table button and select or enter the limiter none none Okay click on okay and of course I get record this would not have happened if I used the other function but it's just easier to do it through the user interface I can click here um and I can just ignore that and click on okay so as you can see this data here is quite complex and I've checked it in in insomnia before so it's um yeah it's it's kind of interesting so this this is the the same result that we're getting in Excel is just in a textual format so it's easier to see um so this is the results key which we just unwrapped and as you can see there there's a whole schema here and um so each of the results is actually an ocean page um and the page has these properties so to get let me just go back to my notion real quick and I have the support ticket and I have the ticket ID and I have description so if I go back to my to my um to my response I can see the properties uh one of the properties is called um okay so here is it's its ID um but I can see this is this is slow so this is probably the um priority property um and then we also have this this um um this Dash here which is probably closed date and then we also have email notifications not working so this is a text okay this is description I've been I've been um I've been missing that so um yeah looks like we we are getting we're getting the um we're getting an an object with all the properties um in this database so this is the property and they they the values so let's go back to excel um and let's let's go back here uh so we're searching for properties now as you can see we have these properties and what I can do is just click on this if you don't need any other records which which um is is the case in in my case I can just uh right click here and remove all columns so this this just makes it easier to to play around with it and I can unwrap it again um and I can see all the properties here yeah here that'll make it much easier so I can click now on okay and this is still a record this is still a record so now what you can do is just unwrap all the properties that you need manually so that's what I'm going to do so click on unwrap here and just select the rich text here and use um original column name as a prefix this is so um the the name of the field is priority and this this will get us priority. T text it's it's a bit easier to see so I can click on okay and I can unwrap it again because it's a list and again and I can now select this plain text and what I'm getting this is what I'm getting this from is um if I go back to insomnia and check the properties I have the properties here I have the priority here and there's this Rich Text property and it has um this is this is actually an array and um there is there there all kinds of fields here but this plain text looks looks the easier to use because there there are no other um child objects benefit so I'm just using this plain text and you can use the same and um yeah that you should be good uh click on Plain text and then click on okay and then finally we have the the priorities here so I can just go and rename that to Priority all right so we have our priorities here and I'm going to do the same for for uh ticket ID so I'll do this one um and uh this one if I go back to to my to my insomnia and search for Ticket ID so ticket ID is of type title so this is not a rich text uh but it's just it's just plain text and it doesn't have the the rich text property but it has the title property and and this is this is still a list and it still contains the plain text so it's it's pretty much the same as uh as before as with uh the priority um but we have to use this title so title use original column that's fine okay unwrap again unwrap again and this one this time select plain text use original column name that's fine so ticket ID is is the name we're looking for okay so we have we we saved two columns now there there there are more ahead um let me just do the rest you can follow the the exact same procedure for other columns and you have if you have a lot of properties here then this will be a lot of work for you but um yeah this is this is how it works on my side so let me come back when I do the rest all right so I'm back as you can see this this um it it created quite a lot of steps here so if we go back to our Advanced editor we can see that there's there's quite a lot of salad here um so there certainly is a better way a shorter way in in um in if if you just do it through Power query but if if you are if you're lazy like me then power then then doing it through the DUI is probably from from the user interface is probably still an easier option um so instead of instead of all this instead of all this crap you could just use list and property accessors um and if you want to know how to do that do let me know in the in the description as well um but you can you could essentially use something like here so this is this is a um a a list on itself as well or a record on itself as well and you can and we access the results property of this record using the the square square brackets and you could do the same thing here and for for arrays for for for for lists you could use the the curly braces operator um like this and just do just do zero um to to get the first element instead of unwrapping the list you could use this this list accessors but anyway if you would like to see how to how to do this in a quicker in a shorter way um in terms of scripts then do let me know but it it it works for me so I'm not changing that um so yeah here is your notion data and now you can go ahead click click on close and load and um play with it in Excel and if you need to refresh it just click here refresh all and that should do the trick all right thank you so much for for staying this long if you stay this long you truly are a hero um we looked at how to get the data from notion into powerbi today um it was it was not the easiest procedure but yeah hopefully you it was useful to you um if you would like to see more videos like this um let me know which services do want me to connect to uh using power query and I can do that as well um I'm happy about answering or or fulfilling any wishes um in terms of in terms of content so yeah just just let me know what you need um anyway thank you so much for staying this long um have a nice day it's it's almost chist Christmas so if you celebrate that then then Merry Christmas to you and to your families and have a good one see you soon cheers bye

Channel: Lovro's BizApps Channel

Views: 5,686

Keywords: excel tutorial, microsoft excel, power bi tutorial

Id: 6RbKfYrjt-8

Length: 29min 40sec (1780 seconds)

Published: Sat Dec 23 2023