Continuously get data from REST API to an updating table (bearer token expires every 24h)

[Mike1991]

Hi,

I'm trying to get data from a REST API that is sending data of a ventilation unit. I can get the instantaneous values (for example room temperature) by the following method: Get data -> Web -> Advanced. Here i put in the URL and the bearer key in an Authentication header.

My goal is to have a table with the historical values, so I will need the table to update automatically every 10 minutes by adding a new line with the timestamp and the room temperature at that given time.

I have two challenges
1) How can I put the data from the API into a table that automatically updates?
2) The bearer token expires every 24 hours, so I need to make a script that can update it by going through a few steps to recieve the new token. Can this be done in Power BI (and how?) or do I need a seperate script (Python) that can retrieve the new token?

I don't have any experience working with API's or Power BI but I have looked into many guides and videos on this topic (which didn't work). I hope someone can point me in the right direction. Thanks!

[TomMartens]

Hey @Mike1991 

From my experience, I think there are different options each with its own challenges. It's also important to know and remember the limitations that exist on the data refresh.

Basically, it's not possible to simply add a row (append) to an existing table, this is only possible if you are using a streaming or push dataset. Here you can use the Power BI Rest API

https://docs.microsoft.com/en-us/rest/api/power-bi/

to write to such a dataset

https://docs.microsoft.com/en-us/power-bi/service-real-time-streaming.

If you find a way to catch the emitted data from the REST API, you can also trigger a Power Automate event: https://powerbi.microsoft.com/it-it/blog/push-rows-to-a-power-bi-streaming-dataset-without-writing-a...

My favorite architecture for these scenarios is to collect data that is emitted by a REST API using some Azure service like the Event hub: https://azure.microsoft.com/en-us/services/event-hubs/
From there you can use other mechanisms to further distribute the collected data, e.g. into an Azure SQL database that will allow some kind of real-time reporting.

You have to be aware that the above solution has additional costs. 

Hopefully, this provides some additional insights and helps to tackle your challenge.

Regards,

Tom