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 :

let
    URL = "https://api.meetup.com/"&MeetupID&"/members?&sign=true&photo-host=public&page=200&offset=",

    //Get total number of memebers
    TotalNumber = let
                    Source = Json.Document(Web.Contents("https://api.meetup.com/2/members?&sign=true&photo-host=public&group_urlname=London-PUG&key=" & APIKey)),
                    #"meta" = Source[meta],
                    #"Converted to Table" = Record.ToTable(#"meta"),
                    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "total_count")),
                    Value = #"Filtered Rows"{0}[Value]
                in
                Value,

    //Find number of calls and Round up
    APICalls = Number.RoundUp(TotalNumber /200),

    //List.Generate - To create the dynamic URL 
    LG=List.Select(List.Generate(
                        ()=>[i=-1,newURL = null], 
                        each [i] <= APICalls,
                        each [i=[i]+1,newURL=URL & Number.ToText(i)&"&Key="&APIKey],
                        each try CallAPIURL([newURL]) otherwise null
            ), each _ <> null),

    
    #"Converted to Table" = Table.FromList(LG, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "name", "status", "joined", "city", "country", "localized_country_name", "state", "lat", "lon", "photo", "group_profile", "bio"}, {"id", "name", "status", "joined", "city", "country", "localized_country_name", "state", "lat", "lon", "photo", "group_profile", "bio"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column2",{{"joined", Int64.Type}}),

    //Added custom column to update UNIX date time to normal date format
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DateJoined", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0,[joined]/1000)),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"joined"}),
    #"Expanded photo" = Table.ExpandRecordColumn(#"Removed Columns", "photo", {"id", "highres_link", "photo_link", "thumb_link", "type", "base_url"}, {"photo.id", "photo.highres_link", "photo.photo_link", "photo.thumb_link", "photo.type", "photo.base_url"}),
    #"Expanded group_profile" = Table.ExpandRecordColumn(#"Expanded photo", "group_profile", {"role", "intro"}, {"group_profile.role", "group_profile.intro"})
in
    #"Expanded group_profile"

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 🙂