Power Query API Help with Generating all the next page tokens.

[Zack92]

Hello Guys, 

I hope you're all enjoying your weekend. I need big help with Power Query API and generating all those next-page tokens. I've created a function called "fx_Caledly_Event_Pagination_WithPageID" and another power query called "nl_Event_Pagination_List" to list all those tokens, but I only get one token back. I'm not a developer and I barely know anything about APIs. I've been relying on online resources and YouTube to get this far. Can anybody lend a hand and help me get a complete list of those next-page tokens? Unfortunately, I can't share the API credentials, but I'll give you some information and screenshots. Hopefully, you'll get the idea.

API response what I hit it on Postman 

Function (fx_Caledly_Event_Pagination_WithPageID)

(PageToken as text)=>
let
    Source = Json.Document(Web.Contents("https://api.calendly.com/scheduled_events/?organization=https://api.calendly.com/organizations/D457896GHEBHGDDL&sort=start_time:desc&next_page_token=" &PageToken, [Headers=[Authorization="************", #"Content-Type"="application/json"]])),
    pagination = Source[pagination],
    #"Converted to Table" = Record.ToTable(pagination),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "next_page_token")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"})
in
    #"Removed Columns"

ScreenShot 

Below where I try to generate all the next page tokens, but only get 1,

let
    Source = List.Generate(
    ()=> [Result = try fx_Caledly_Event_Pagination_WithPageID("") otherwise null, PageNum=1],
    each [Result] <> null,
    each [Result = try fx_Caledly_Event_Pagination_WithPageID(List.Single([Result])) otherwise null, PageNum=[PageNum]+1 ],
    each [Result]
),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Value"}, {"Column1.Value"})
in
    #"Expanded Column1"

ScreenShot

I've been struggling with this all day and I just can't seem to figure it out. I could really use some help in generating a complete list of all those next page tokens. I'd really appreciate any assistance you can offer.

Thank you so much!

[danextian]

Hi @Zack92 

 List.Generate(
    ()=> [Result = try fx_Caledly_Event_Pagination_WithPageID("") otherwise null, PageNum=1],
    each [Result] <> null,
    each [Result = try fx_Caledly_Event_Pagination_WithPageID(List.Single([Result])) otherwise null, PageNum=[PageNum]+1 ],
    each [Result]

You mentioned that you only get one page. That is because the first iteration is not equal to null so it stops right there.

()=> [Result = try fx_Caledly_Event_Pagination_WithPageID("") otherwise null, PageNum=1],
each [Result] <> null

You might want to try otherwise.  You'll want it to run until the page number becomes null. If this doesn't work, I would just create a list of numbers/tables of numbers to iterate through. Example {1..10}. This creates a list of numbers from 1 to 10.

[Zack92]

@danextian 
thank you for taking a look at my issue. I'm sorry, but I'm having trouble understanding your explanation. It could be because I don't have much knowledge about API development. Could you please explain the difference between your "list generate" code and "my list generate"? Can you please tell me how to fix that issue so it will not stop? Note- When I refresh my query, the result page token gets a new value. 

[danextian · Accepted solution]

Hi @Zack92 

My suggestion is to create a list of numbers containing the page you want to iterate through. Of course, the list is not going to be exact - it may contain more or lesser than the require page numbers as we don't know how many pages there exactly are.  The code would be more or less like this:

let
  //list of numbers from one through 15  
  Source = {1 .. 15} ,
  #"Converted to Table" = Table.FromList(
    Source,
    Splitter.SplitByNothing(),
    {"Numbers"},
    null,
    ExtraValues.Error
  ),
  #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Numbers", type text}}),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "Custom",
    each
      let
        x = [Numbers]
      in
        Json.Document(
          Web.Contents(
            "https://api.calendly.com/",
            [
              RelativePath = "scheduled_events/?organization=https://api.calendly.com/organizations/D457896GHEBHGDDL&sort=start_time:desc&next_page_token="
                & x,
              Headers = [Authorization = "************", #"Content-Type" = "application/json"]
            ]
          )
        )
  )
in
  #"Added Custom"