Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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"
Proud to be a Super User!
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.
Proud to be a Super User!
@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.
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"
Proud to be a Super User!
User | Count |
---|---|
94 | |
78 | |
73 | |
64 | |
60 |
User | Count |
---|---|
106 | |
97 | |
76 | |
63 | |
61 |