Category: Power Query Page 1 of 2

In preparing for the Global Power BI and Fabric Summit this year, I got a little nostalgic. I went back to my presentation and early blog posts on connecting to REST APIs. Two years, several blog posts, and a book on Power Query later, I felt it was time to add a fifth part to this series. I realized that I left out one additional step I could take to optimize my query a little more to allow me to squeeze the most out of my API call limits.

I will be picking up where we left off with part 4 of how to connect to data with REST APIs. However, if you are new to my blog, you can start from part 1 and get caught up. If you recall, I am limited to 100 API calls a day with my scenario. If I am watching more than 100 stocks, I might need to consider moving to the next subscription level. However, I have an option to squeeze even more out and potentially return 1,000 stock symbols. This is a bonus as I do not need to increase my subscription!

What Did Dom Miss in Part 4?

To note what I missed, I need to go back to part 2 on REST APIs. You must Read the Fine Manual! In reading the documentation, I realized that I could batch multiple stock symbols at once. Instead of making individual calls for each symbol, I can group ten of them in a single call.

With this knowledge in hand, I set out to take my query one step further and get more out of my subscription. It is not as straight forward as it may seem, but with a little manipulation we can make it work.

Prepare my List

We already have a spreadsheet of stock symbols we have brought in. We need to do start by removing all of the columns except the stock symbol. Just right click on the symbol column and remove other columns. Once that is complete, insert an index column that starts from 1. Your query should look like this:

Next, we need to add a batch column. We will need to identify every 10th row and assign it an index number. We will need to create a custom column for this. Using a modulo division function, we will identify every tenth row. If it is the tenth row, the remainder will be zero. If the modulo division equals zero, we will divide the index by 10. Otherwise, we will return a null value. The formula looks like this:

if 
  Number.Mod([Index], 10) = 0 
  then Number.IntegerDivide([Index], 10) 
  else null

Next, we just need to use the fill up feature to make sure the rows are assigned to a batch. The last few rows will still be null. This is not an issue as it will still be its own batch. The result looks like this:

With our batch in place, we can remove the index column. Next, we need to group our symbols together.

Use Group By to Bring Symbols Together

Going back to our documentation, each symbol must be separated by a comma. We will use the group by function to bring ten rows at a time together into a single column. We will select our batch column and select the group by function in the transform ribbon. Using the sum aggregation, we are going to summarize our symbol column. Naturally, this will result in an error, but we will fix that next.

Next, we will need to update the M code in the advanced editor. We need to replace the List.Sum() function with the Text.Combine() function. Our code for this step will look like this:

Table.Group(#"Removed Columns", {"Batch"}, 
  {{"Symbols", each Text.Combine([Symbol], ","), 
  type nullable text}})

The result an updated query with our stock symbols combined:

Now that the hard part is over, we just need to apply our function.

Functions and REST APIs

We already created a function in part 3 of my REST APIs series. We just need to apply it to our new query. Go to the add column ribbon and insert a column from function. Using the symbols column for the parameter, we can query all of the symbols in the cell.

All that is left to do is expand the results so they can be leveraged in the model:

And just like that, we turned twenty five API calls to three. Not too shabby!

Conclusion

It is extra work, but it is worth while if you can save a few dollars and minutes with your refresh. It may seem excessive, but it might make the difference. Besides, I could not resist a post where I tell people to Read the Fine Manual as I know my friend Mike will get a good laugh out of it again!

Have you ever used the group by function in Power BI? Or ever extended like this? Did I miss another way to optimize this query? If so, tell me in the comments below!

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.

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!

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.

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!

In my last article, I shared how to scale a query by using a function in Power Query. However, I noted there might be some challenges with this approach and considerations you should keep in mind when connecting to REST APIs. Some of these considerations are simple and are similar to normal query optimization activities. While not as technical as other articles, I feel it is important to take some time to focus on potential challenges you might encounter when leveraging REST APIs.

Limits, Throttling, and Speed

When reading your documentation, you might see words such as “limits” and “throttling” and wonder what they are. You will build your report and everything will be fine until one day your refresh fails. In reviewing the refresh errors it will suddenly make sense what “limits” and “throttling” mean.

To ensure APIs are not overburdened, limits and throttling are employed to prevent abuse. This protects the endpoints and provides a reliable service. Understanding how to navigate them are essential to prevent your refreshes from failing as your solution scales.

What are API Request Limits?

API limits restrict the number of calls made in a day, week, or month. If you signed up for the Yahoo Finance API, you notice they have three tiers of membership. The free tier limits your calls to 100 per day. Because of how we built our function, you would have to limit your report to 100 stocks and only refresh it once a day.

Now there are additional tiers that you can purchase which increase your limits. The Yahoo Finance API offers tiers that give you 20,000 or 75,000 calls per month which might help accommodate your use case but requires an additional financial investment. But even if you increase your subscription, note that these tiers still have a hard limit. Meaning that you might need to keep purchasing a higher tier if you blow through your request limit.

To be a good steward of your subscription, you will want to regularly monitor your API usage and verify that you are not burning through your quotas too quickly. It never fails – you run out of requests just when you need the data the most.

What is Throttling?

APIs use throttling to protect endpoints from being heavily queried over a short period of time. Queries are limited to the number of calls made per minute, per 15 minute window, or per hour. This protects endpoints from rogue code that could degrade the service because of abuse.

With the Yahoo Finance API, our documentation states that there is a rate limit of 300 calls per minute. If you had a list of 1,200 stocks it would take at least four minutes for the refresh to complete. (1,200 stocks divided by 300 = 4 minutes) Just like how every API is unique, they manage throttling differently.

Last year I built a Power BI report that leveraged Google’s mapping APIs to get coordinates and street views of addresses in my town. Google enacted throttling to limit requests to 100 per minute. When refreshing my data, I hit my 100 request limit and the API paused until the full minute had passed. In doing so, I was able to proceed with the refresh. With 3,500 houses in town, it took 35 minutes for the refresh to complete. It was slow, but it worked great.

On the other side of the spectrum, the Microsoft Graph APIs have a limit of 10,000 calls per 10 minute period. When I ran over this limit, instead of the API pausing, I ended up receiving responses with a 429 error stating I reached the throttling limits. In order to manage this, I had to get a little more creative with my query to make sure I did not receive that error.

Impact on Refresh Time

Regardless of limits, you will need to consider refresh time when using REST APIs. If your primary data sources reside in SQL servers, this will be an adjustment for you. Normally you just cast a wide net with your query and bring in everything you can and work from there. With APIs, you want to narrow that scope to only what you really need. But with REST APIs, the refreshes tend to be slower. Adding some filtering will speed up your queries and make them more efficient.

If you are in a situation where you need a large amount of data from an API, you might want to consider leveraging Azure Data Factory or Databricks to incrementally replicate that data into a data warehouse instead.

Paging Queries

For this series of articles, our endpoint was fairly basic. It only brought back one row of data for each stock symbol. This was by design because our overall goal was to cover the basics. As you explore other APIs, you might find endpoints that bring back several rows at a time.

If you expand your horizon with REST APIs, eventually you are going to run into something called paging. Simply put, paging is the splitting of responses over multiple queries. Similar to limits, this is to prevent overburdening the endpoint with your query.

As an example, let’s say you are querying an endpoint that has 50,000 records. Paging has been enabled on this query and is limiting the responses to 1,000 records at a time. In order to receive all 50,000 records, you will need to build logic into your query to pull each page individually.

So how do you know your endpoint is using paging? It is all about the documentation. If an endpoint has pagination, there are usually a few parameters that will tip you off. If you see a parameter called “PageNo”, “PageSize”, or “Top”, you could run into paging.

It is also important to consider that you could avoid pagination with quality filtering. If your request is targeted and removes the noise of other records, it could avoid this problem all together.

In the coming weeks I will be doing an article specifically on paging, so keep an eye out for it!

Token Authentication

Another challenge you might encounter is token based authentication. Instead of passing an API key for authentication, you would need to call another endpoint to receive a token. Once you have the token, you would pass it through a header.

If you encounter this situation, you would need to create a function in Power Query that would solely return a token. While it takes a few extra steps, it is likely reusable with other endpoints in your report. In addition to that, it will simplify your query code and keep it tight and compact.

Conclusion

Congratulations for making it to the end of this series. There are a lot of challenges when implementing REST APIs as a data source but the juice is worth the squeeze. Taking yourself out of the mix and automating my refreshes is the end goal. And while the experience may not be as efficient as a SQL data source, it still gets the job done.

Have you been following along? Were you able to successfully connect and extract data from Yahoo Finance? How about other REST APIs? Let me know in the comments below!