I have a Database Lite apllication called Airtable linked via its api into Power BI. The Airtable API has 100 record limit on each pull, so you have to paginate on subsequent calls. I figured this out with a little google help. However, I now have a list of 7 pages for a total of 679 entires. I did figure out how to join them all into a single table by hand, but I would like to reproduce this on any Airtable Base I call. That means the amount of records that are called might be 50 or might be 1200, so thats one page to 12 pages. My understanding is that List.Generate will handle this if I know how to construct it. This is literally day two looking at the syntax for all of Power Bi, so I need some help understanding haha. Here's how I did it without list.generate. Sorry if its a mess.
Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0], // Start Value
each [Last_Key] <> null, // Condition under which the next execution will happen
each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution
WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/<mytable>/Budget%20Descriptions%20and%20Cost?",[Headers=[Authorization="... <mykey>"]]))else Json.Document(Web.Contents("https://api.airtable.com/v0/<mytable>/Budget%20Descriptions%20and%20Cost?offset="&Last_Key,[Headers=... <mykey>"]])), // retrieve results per call
Counter = [Counter]+1// internal counter
],
each [WebCall]
),1),
#"Combined to Table" = Table.FromRecords({Pagination{0},Pagination{1},Pagination{2},Pagination{3},Pagination{4},Pagination{5},Pagination{6}})
in
#"Combined to Table"
Edit 1
Edited out the block of code because I realized it was mostly extranious. I just want to turn that "Table.FromRecords" into a List.Generate statement that can process a variable amount of "Pagination".
