Hi everyone,
Since i've been working on this issue for some time now, i thought i might try my luck here.
I haven't really found anything that exactly solved my issue in related posts. and i'm struggling to modify other soutions since im quite new.
Situation: I'd like to get all pages via a Rest-API and merge them into one table. It is about 22 Pages max à 100 rows, so quite manageable. However the api doesnt provide much info. and i can't access a next_link or any helpful functions like $page since im not on OBD but connecting via WEB and Access-Token.
The code:
let
url= "https://XYZ.mocoapp.com/api/v1/schedules?from=2021-01-01&to2021-12-31&page=",
token = [Headers=[Authorization="Token token=XXX"]],
Source = Json.Document(Web.Contents(Text.Insert(url,82,Text.From(PageValue)), token)),
super_list = List.Generate(()=>Source, CheckEmpty(Source), PageValue=PageValue + 1),
#"Converted to Table" = Table.FromList(super_list, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "date", "comment", "am", "pm", "symbol", "assignment", "user", "created_at", "updated_at"}, {"id", "date", "comment", "am", "pm", "symbol", "assignment", "user", "created_at", "updated_at"}),
#"Expanded assignment" = Table.ExpandRecordColumn(#"Expanded Column1", "assignment", {"id", "name", "customer_name", "color", "type"}, {"assignment.id", "assignment.name", "assignment.customer_name", "assignment.color", "assignment.type"}),
#"Expanded user" = Table.ExpandRecordColumn(#"Expanded assignment", "user", {"id", "firstname", "lastname"}, {"user.id", "user.firstname", "user.lastname"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded user",{{"id", Int64.Type}, {"date", type date}, {"comment", type any}, {"am", type logical}, {"pm", type logical}, {"symbol", type any}, {"assignment.id", Int64.Type}, {"assignment.name", type text}, {"assignment.customer_name", type text}, {"assignment.color", type text}, {"assignment.type", type text}, {"user.id", Int64.Type}, {"user.firstname", type text}, {"user.lastname", type text}, {"created_at", type datetime}, {"updated_at", type datetime}})
in
#"Changed Type"
what im trying is via PageValue parameter. to just create a for loop that appends all list via list generate as long as the list is non-empty. else fail.
I tried requesting a page that is empty and the server responded with an empty list, not an error.
currently the list.generate is very unhappy with my condition-clause. Im using List.isEmpty as a check in CheckEmpty Function. However: it gives me expression error: We cannot convert a value of type List to type Number.
