Accessing API with Oauth2 using M language in Power BI

Nowadays, all we can imagine it is available online, and it is a great temptation for cybercriminals. To protect our data, every day new security systems are being created. OAuth 2.0 is one of them. According to OAuth's website, "OAuth 2.0 is a protocol that allows a user to grant a third-party web site or application access to the user's protected resources, without necessarily revealing their long-term credentials or even their identity."

In summary, the client asks one server for authorization, which answers with one token, this token needs to be sent again along with the requisition which returns the data.

These requests are done through API (Application Programming Interface), which is a computing interface that defines interactions between multiple software intermediaries.

Next, a small demonstration.

In the following paragraphs, it will be shown how to access an API with Oauth2 authentication using M language in Power BI.

According to Microsoft, Power Query Formula Language or M only, is a language that has around 700 functions and can serve both to prepare ETL and mining data.

Procedures:

1-Click in Edit Queries

2- Click in New Sources > Blank Query

3 - Rename your query if you want and click in Advanced Editor

4- It is all you need to be happy.

The first step is to make a requisition of authentication where needs to be sent some information. Each API is different from each other, therefore you need to read the API specification before using it. In my case, to request authorization, I need to send the Token address, Username, and Password.

To organize the information, it is suggested to create some parameters to be used instead of information

5 - Manage Parameters > New Parameter

6 - Three parameters have been created

7- Now, we have this, three parameters and one code

8 - The code

9- The return of the request is here

10 - Double click and you will have some information. The Token is included

Once the return is done, another request has to be made. For this, we need to change the code above. As it can be noticed, the API sent three pieces of information, however, only one is needed, the idToken. Therefore, it will be inserted in the code to convert the return to JSON and another one to return only the idToke information.

11- Adapting the code

12- Now, the return is just the token

Now, let's go to the next request sending the Token, which is the key authorization, to finally access data. A new parameter is needed to set urlRequest

13- The code has been adapted the code again

14- In the end, we have information that came from an API request, authentication by Oauth2 technology using M language

Link to the code