Forum Discussion

Zack92's avatar
Zack92
Helper III
2 years ago
Solved

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

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!

Need Help
Show and Tell
Tips and Tricks
  • danextian's avatar
    danextian
    2 years ago

    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"

     

3 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    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's avatar
      Zack92
      Helper III
      2 years ago

      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's avatar
        danextian
        Super User
        2 years ago

        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"