Earlier this week I spoke about the Basics of Connecting to REST APIs at the Global Power BI Summit. A lot of people found it valuable, but wanted to know more about how to use the POST method to get data. Specifically, they wanted to know how to add a payload with the request to make it work. Since there was a lot of interest, I decided to write an article on this topic and share it with everyone.

For this article, I will be using the Microsoft OAuth2 endpoint to get an authorization token. You can find detailed documentation on the endpoint here. I will not be going into the details of the process to create an app registration or apply it in the Microsoft environment as that will take some time to cover. Instead, I decided to focus on the most important part of the request – the POST Method!

No/Low Code No More!

For many, this will be the scariest part of the entire process. This might be your first time ever writing code for a query from scratch. We do not have a connector to help us with this process. Fear not as we will walk through this entire process together and break it down. So to get everything started, go to Get Data and select Blank Query.

Adding a Blank Query to your report.

Once you select Blank Query, you will be redirected into Power Query with a blank canvas. I like to quickly update the query name on the right hand side before I forget. I am calling mine “Get-AuthToken”. Once that is in place, we will open up the Advanced Editor in the home ribbon. That is where the real fun will begin…

POST Method Query

Now that the Advanced Editor open, we are going to start writing out the query. We will need to write out a web connector query and a message body to make this work. I like to start with the web connector and build out from there.

Building the Initial Connection Query

Currently, we have Source = “” in our editor window. Those quotes are going to be the base of our query. We are going to start by adding the endpoint url in between the quotes. We should see something that looks like this:

let
    Source = "https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token"
in
    Source

In order to specify how we are connecting to the endpoint, we will wrap it with Web.Contents:

let
    Source = Web.Contents("https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token")
in
    Source

After that is in place, we need to add our headers. These are specified in the documentation and will be unique to your API. This example uses two headers and they are required to achieve the desired result. Note the comma between the url and the headers.

let
    Source = Web.Contents("https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token", [Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"]])
in
    Source

Lastly, because the header is specifying the response in JSON, we will wrap the entire string with Json.Document to translate the response.

let
    Source = Json.Document(Web.Contents("https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token", [Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"]]))
in
    Source

With that code in place we have our base query built. If you want to save some typing, you could always use an existing query as a template. Just copy and paste it into the blank query and make your changes.

Now that we have the base query written out it is time to focus on the message body.

Body Building without Protein

The body comes by a few different names. You might see it as message body, query, payload, or simply “The Data”. Essentially, it is additional information that is passed so the endpoint works as expected. While you don’t have to lift weights for this step, you might find it just as frustrating. I have tried this a few different ways, but found this to be the easiest method.

The first thing we will do is create a line right above our Source line and below the let line. From here, we will enter ContentBody = “”, as a place holder. Make sure you add the comma at the end of the line!

let
    ContentBody = "",
    Source = Json.Document(Web.Contents("https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token", [Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"]]))
in
    Source

My example requires five different fields be specified in the body. The formula is simple: field name = field data. We will add an ampersand at the end of the line and return to the next line to make it easier to read.

let
    ContentBody = 
        "Content-Type=application/x-www-form-urlencoded&
        scope=https://graph.microsoft.com/.default&
        grant_type=client_credentials&
        client_id=<CLIENTID>&
        client_secret=<CLIENTSECRET>",
    Source = Json.Document(Web.Contents("https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token", [Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"]]))
in
    Source

As a best practice, I keep elements that might need to be changed at the bottom for easier access. This example leverages a client secret that will need to be updated over time. To make it easier to access, I leave it at the bottom.

Add the Body to the Web Connector

To finalize your query using the POST Method, you have to add the message body to the source line. We need to pass the body as content. However, I need to convert it into binary. We are just going to add Content=Text.ToBinary(ContentBody) inside of the first set of brackets so it passes the body in the web connector.

let
    ContentBody = 
        "Content-Type=application/x-www-form-urlencoded&
        scope=https://graph.microsoft.com/.default&
        grant_type=client_credentials&
        client_id=<CLIENTID>&
        client_secret=<CLIENTSECRET>",
    Source = Json.Document(Web.Contents("https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token", [Content=Text.ToBinary(ContentBody), Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"]]))
in
    Source

Now that everything is in place, we can click Done and let the query do its magic!

Response from my POST Method query that brings back an access token for authentication.

To take things to the next level, I can drill down on the actual token by right clicking and selecting drill down. Next I will convert the query to a function by adding ()=> above the let line. In doing so, I will be able to leverage it for multiple endpoints that require token based authentication with a simple call.

()=>
let
    ContentBody = 
        "Content-Type=application/x-www-form-urlencoded&
        scope=https://graph.microsoft.com/.default&
        grant_type=client_credentials&
        client_id=<CLIENTID>&
        client_secret=<CLIENTSECRET>",
    Source = Json.Document(Web.Contents("https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token", [Content=Text.ToBinary(ContentBody), Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"]])),
    access_token = Source[access_token]
in
    Source

Make Life Easier with Parameters

I plan to write a more detailed article on Parameters inside Power Query but I want to share a little tip that will make your life much easier. With this example, we are connecting to an app registration inside Azure Active Directory through a Client ID and Client Secret. The Client Secret will expire at some point and needs to be refreshed. To make your life easier, you can add your dynamic values as Parameters so you can update the query quickly.

Go to your home ribbon and click Manage Parameters. Click new and add your parameter name. Lastly, add your parameter value and click OK when finished.

Setting up parameters to simplify the process to update my client secret.

Now we just need to replace the values in our query. We will have to use the ampersands to concatenate our values together so they come together in a single string.

()=>
let
    ContentBody = 
        "Content-Type=application/x-www-form-urlencoded&
        scope=https://graph.microsoft.com/.default&
        grant_type=client_credentials&
    	client_id=" & ClientId & "&
        client_secret=" & ClientSecret ,
    Source = Json.Document(Web.Contents("https://login.microsoftonline.com/" & TenantId & "/oauth2/v2.0/token", [Content=Text.ToBinary(ContentBody), Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"]])),
    access_token = Source[access_token]
in
    access_token

Now if I need to update my client secret, I can just update the parameter instead of breaking open the Advanced Editor. I can even do it from the settings view in the Power BI Service. But that will have to wait for another article.

Final Thoughts on the POST Method

It is important to note that not all POST Method endpoints are meant for querying in Power BI. The primary purpose of the method is to create items. However, it is not unheard of for querying rows of data. Before you do anything with an endpoint that uses the POST Method make sure you review the documentation. You might accidentally write data to your endpoint if you are not careful!

Do you have a use case for querying using the POST Method? Any favorite endpoints? If so, leave a comment below!