Power BI - Collect data from API's

Date: 06.04.2020

I've been strugling for a while with collecting data from API's to Power BI. It works fine in Power BI Desktop, but once I publish to Power BI Service, the authorization is removed and Power BI Service cannot update. Here is my solution.

Power BI can collect data from many sources, I use sources like Azure Logs, Cosmos DB, Excel and different API's like Atlassian Statuspage.io and SuperOffice CRM.

The problem with API's is related to authorization tokens. You simply add them in the wizard in Power BI Desktop like this:

...and it all works perfectly fine until you publish to Power BI Service, then the HTTP request header parameters is stripped away. In Power BI Service there is also no way of adding the header parameters.

I found my solution (works on all the API's I'm using) deep in a forum thread on Microsofts own Power BI discussion forums. It was not marked as a solution, but it worked much better for me than any other solutions I found.

Relative Path

The solution is simply to edit the Power Query M and using a realtive path like this example using the Atlassian Statuspage.io API:

Source = Json.Document(Web.Contents("https://api.statuspage.io", [Headers=[Authorization="XXXXXXX-XXXX-XXX-XXXX-XXXXXXXX"], RelativePath="/v1/pages/XXXXXXXXX/subscribers.json"])),

What I'm basically doing here is to go to the root folder, getting a 200 OK (so Power BI basically thinks I'm using an anonymous connection), then adding the header parameters before using the rest of the API path as a relative path.

Renewing tokens

So this trick works perfectly as long as I don't need to refresh the tokens. Many API's using Bearer tokens, give the tokens a very short lifetime. We at SuperOffice for example only let our own API tokens live for an hour.

How to make Power BI automatically update the tokens... I haven't figured out yet, I'll keep you posted in a later article ;-)