Home Personal Dynamically update Offset value for API calls using Power BI / Power Query

Dynamically update Offset value for API calls using Power BI / Power Query

written by Prathy Kamasani August 2, 2017
Dynamically update Offset value for API calls using Power BI / Power Query

If you are a Power BI user group organiser, then it’s kind of obligatory to create a Power BI dashboard on Meet up data. Like many, I thought I would create a Power BI template for London PUG Power BI Meet Up (That’s another blog post!). However, pulling data from API services is never fun. When I started importing data from Meet Up API, at first it was seamless; quite quickly I pulled members data from London PUG Meetup site. Not far after that, I realised, I only had 200 members. I increased Page value to 1500, but still, still, I could see only 200 rows. After a bit of searching, I found this forum – https://stackoverflow.com/questions/35121504/get-more-than-200-group-members-from-the-meetup-api  .

Based on this, I could get only 200 results.  So I need to find a way to call the API with a dynamic offset value using Power Query. The total number of Offset value depends upon the page size and number of members. So first I need to find the total number of members in London PUG Meetup Group then calculate how many offsets I needed. Like any other language, there are many ways I could do this but I decided to go for List.generate() function.

Dynamically update Offset value for API calls using Power BI:

I read so many nice things about M function List.Generate(), but I don’t think I have ever used it properly. There are some really nice blog posts explaining list.Generate() function functionality. These are my favourite: PowerPivotPro, DataChant, Excel Inside and Excelando. If you want to understand the function, these blog posts are a good place to start. What I am showing in this blog post is to show how we can use this with API. Very similar to what they mentioned in their blog posts just a different data source, but I kinda have to write this post so I can remember for my reference.

Here is the M code I used :

I created two Parameters, one for MeetupID and another one for API Key. Then I kind of used a subquery to find TotalMembers. I divided the Total number with 200 to calculate the number of API calls I needed. After that List.Generate() handles all the hard work.

It’s not only Meetup API, there are many API services which use filters like pages and offset, and List.Generate() works pretty well.

Prathy 🙂



You may also like

Leave a Reply

%d bloggers like this:
Skip to toolbar