Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Solved importing it although it resulted in a nested Table, so not as easy to work with. which i'm what im doing now. Feel free to alter the code. I'm sure it can be optimised.
This is what i did in the main Query :
#"super_list" = List.Generate(()=>[page=1,Funct = Convert_Page_Table(1)], each (try[Funct])[HasError]=false, each [page=[page]+1,Funct=Convert_Page_Table([page]+1)]),
#"Page_list" = Table.FromRecords(#"super_list"),
This is the custom function I implemented :
(PageValue)=>
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)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
Solved importing it although it resulted in a nested Table, so not as easy to work with. which i'm what im doing now. Feel free to alter the code. I'm sure it can be optimised.
This is what i did in the main Query :
#"super_list" = List.Generate(()=>[page=1,Funct = Convert_Page_Table(1)], each (try[Funct])[HasError]=false, each [page=[page]+1,Funct=Convert_Page_Table([page]+1)]),
#"Page_list" = Table.FromRecords(#"super_list"),
This is the custom function I implemented :
(PageValue)=>
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)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |