CODEWITHSUNDEEP
powerbipowerquery
gujjar
gujjar

Reputation: 11

REST Api pagination Loop... Power Query M language

I am wondering if anyone can help me with api pagination... I am trying to get all records from an external api but it restricts me with only getting maximum of 10. There are around 40k records.. The api also does not shows "no.of pages"(response below). hence i cant get my head around a solution. There is NO "skip" or "count" or "top" supported either.. i am stuck...and i dont know how to create a loop in M language until all records are fetched. Can someone help me write a code or how it can look like

Below is my code.

let
  Source = Json.Document(
    Web.Contents(
              "https://api.somedummy.com/api/v2/Account",
  
               [ 

                    RelativePath ="Search",
                    Headers =
                      [
  
                          ApiKey = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx",
                          Authorization = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
                          #"Content-Type" = "application/json"
                      ],

                    Content=
                      Json.FromValue(

                        [key="status", operator="EqualTo", value="Active", resultType="Full"]
                      )
                          

              ]
     
                          )
                )
in
Source

and below is output

"data": {
        "totalCount": 6705,
        "page": 1,
        "pageSize": 10,
        "list":[

Upvotes: 1

Views: 1499

Answers (1)

Marc Pincince
Marc Pincince

Reputation: 5202

This might help you along your way. While I was looking into something similar for working with Jira, I found some helpful info from two individuals in the Atlassian Community site. Below is what I think might be a relevant snippet from a query I developed with the assistance of their posts. (To be clear this snippet is their code, which I used in my query.) While I'm providing more of the query (the segment of which is also comprised of their code) below, I think the key part that relates to your particular issue is this part.

  yourJiraInstance = "https://site.atlassian.net/rest/api/2/search",
  Source = Json.Document(Web.Contents(yourJiraInstance, [Query=[maxResults="100",startAt="0"]])),
  totalIssuesCount = Source[total],
  // Now it is time to build a list of startAt values, starting on 0, incrementing 100 per item
  startAtList = List.Generate(()=>0, each _ < totalIssuesCount, each _ +100),
  urlList = List.Transform(startAtList, each Json.Document(Web.Contents(yourJiraInstance, [Query=[maxResults="100",startAt=Text.From(_)]]))),
  // ===== Consolidate records into a single list ======
  // so we have all the records in data, but it is in a bunch of lists each 100 records
  // long. The issues will be more useful to us if they're consolidated into one long list

I'm thinking that maybe you could try substituting pageSize for maxResults and totalCount for totalIssuesCount. I don't know about startAt. There must be something similar available to you. Who knows? It could actually be startAt. I believe your pageSize would be 10 and you would increment your startAt by 10 instead of 100.

This is from Nick's and Tiago's posts on this thread. I think the only real difference may be that I buffered a table. (It's been a while and I did not dig into their thread and compare it for this answer.)

let
  // I must credit the first part of this code -- the part between the ********** lines -- as being from Nick Cerneaz (and Tiago Machado) from their posts on this thread:
  // https://community.atlassian.com/t5/Marketplace-Apps-Integrations/All-data-not-displayed-in-Power-BI-from-Jira/qaq-p/723117.
  // **********
  yourJiraInstance = "https://site.atlassian.net/rest/api/2/search",
  Source = Json.Document(Web.Contents(yourJiraInstance, [Query=[maxResults="100",startAt="0"]])),
  totalIssuesCount = Source[total],
  // Now it is time to build a list of startAt values, starting on 0, incrementing 100 per item
  startAtList = List.Generate(()=>0, each _ < totalIssuesCount, each _ +100),
  urlList = List.Transform(startAtList, each Json.Document(Web.Contents(yourJiraInstance, [Query=[maxResults="100",startAt=Text.From(_)]]))),
  // ===== Consolidate records into a single list ======
  // so we have all the records in data, but it is in a bunch of lists each 100 records
  // long. The issues will be more useful to us if they're consolidated into one long list
  // 
  // In essence we need extract the separate lists of issues in each data{i}[issues] for 0<=i<#"total"
  // and concatenate those into single list of issues .. from which then we can analyse
  // 
  // to figure this out I found this post particulary helpful (thanks Vitaly!):
  // https://potyarkin.ml/posts/2017/loops-in-power-query-m-language/
  // 
  // so first create a single list that has as its members each sub-list of the issues,
  // 100 in each except for the last one that will have just the residual list.
  // So iLL is a List of Lists (of issues):
  iLL = List.Generate(
     () => [i=-1, iL={} ],
     each [i] < List.Count(urlList),
     each [
         i = [i]+1,
         iL = urlList{i}[issues]
     ],
     each [iL]
 ),
  // and finally, collapse that list of lists into just a single list (of issues)
  issues = List.Combine(iLL),
  // Convert the list of issues records into a table
  #"Converted to table" = Table.Buffer(Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error)),
  // **********

Upvotes: 1

Related Questions