{"id":98,"date":"2019-02-06T19:40:00","date_gmt":"2019-02-06T18:40:00","guid":{"rendered":"http:\/\/daxter.nl\/?p=98"},"modified":"2020-09-11T20:21:04","modified_gmt":"2020-09-11T19:21:04","slug":"refresh-any-oauth2-api-from-powerbi-online-dataflows-using-azure-functions","status":"publish","type":"post","link":"http:\/\/daxter.nl\/en\/refresh-any-oauth2-api-from-powerbi-online-dataflows-using-azure-functions\/","title":{"rendered":"Refresh (any) OAuth2 API from PowerBI Online \/ Dataflows, using Azure Functions"},"content":{"rendered":"\n

Some time ago, I had an intake with a new client; he wanted to analyze his financial data coming from Exact Online, using Power BI. After a quick search, I thought there was no problem. There were many other companies who offered this service and the API documentation<\/a> was comprehensive. I even found a step-by-step tutorial<\/a>. 
But, during development, there were many problems to overcome in order to make it work. To share my experience with the rest of the world, I decided to write this blog (my first one!). I\u2019ll show you how to connect an API which uses OAuth2 authentication to PowerBI, refresh the data online and probably the most beautiful part: for free*. Key to this solution is the use of Azure Functions, to act as a gateway between the PBI Service and the API.<\/p>\n\n\n\n

Note: all assumptions and conclusions about (the lack of) technical features are subject to change. Power BI is developing very fast, and by the time you read this, it could be obsolete.<\/em><\/p>\n\n\n\n

The Problem<\/strong>
The first problem is that PowerBI out-of-the-box doesn\u2019t support OAuth2 API sources<\/a>. It can\u2019t handle the authentication flow, using the standard Get Data > From Web<\/em> option. There are some options<\/a> get the data via complex M (PowerQuery) code, but then online refresh would still be an issue. If you want to use OAuth2, you must write (or buy) a Custom Connector. Unfortunately, with a custom connector, you can only refresh the data with an On-Premise Data Gateway, which requires a (windows) pc online at refresh time. I was looking for a solution to connect a SaaS application to Power BI, without the need of any resource on-premise.<\/p>\n\n\n\n

The Solution<\/strong>
To refresh the data directly and get rid of the OAuth2 auth flow, I\u2019ve used Azure Functions<\/em><\/a>. In short: with Azure Functions, you can execute code in the cloud (Azure). More beautiful; it can act as an API which you can use to send or receive data. 
In plain text: the original request will not be sent directly to the API, but to the Azure Function. This piece of code will handle the authentication and sends the original request to the API. The response will be returned as result of the Azure Function. The Azure Function acts as a gateway between the caller (PowerBI) the service.<\/p>\n\n\n\n

\"\"\/<\/figure>\n\n\n\n

The Code
<\/strong>The Azure Function contains three parts; the main function call with the logic, a separate access code routine to perform the authentication request and a code block to store the response refresh code into the Table Storage.<\/p>\n\n\n\n

To call the function, you must provide the original API call in the name <\/em>query parameter. So the call to 
https:\/\/yourfunctionurl\/function?name=\/financial\/GLAccounts<\/code>
will be translated to
https:\/\/start.exactonline.nl\/api\/v1\/division\/financial\/GLAccounts<\/code><\/p>\n\n\n\n

These steps will follow:<\/p>\n\n\n\n