Hello everyone!
New to Power BI, I'm currently followed a online course and that helps a lot, but it didn't cover page limitations of a data source.
There are many topics about this already but non seems to fix my problem.
One of the issues with the other topics is that my source doesn't seem to accept any other authentication method than I'm using in the example below; and I can't propperly combine the other solutions with my needed link.
The source is a API. I've added the authentication in 2 parameters (X-Api-Env and X-Api-Key) because we have to change them every 6 months.
The source has a standard page limit of 100, but can be enlarge to 1000 using ?limit=1000 in the link.
You can offset to the next batch using ?offset=1000.
This specific database currently has bit more that 65k rows and increases weekly with about 1500 rows.
I could manually create 66 sources with a change of the offset in each and the combine them. But I would need to update this every week, so that won't be handy.
I'm looking for a code that automatically downloads all rows.
The table this download creates has 3 columns: count, limit and offset. The limit and offset is filled with the value entered in the sources. The count shows the total rows the entire database has.
My current working code for the rows from 1001 to 2000 is:
let
Source = Json.Document(Web.Contents("https://api.vplan.com/v1/time_tracking?limit=1000&offset=1000", [Headers=[#"X-Api-Env"=ApiEnv, #"X-Api-Key"=ApiKey]])),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded data" = Table.ExpandListColumn(#"Converted to Table", "data"),
#"Expanded data1" = Table.ExpandRecordColumn(#"Expanded data", "data", {"id", "card_id", "activity_id", "user_id", "start", "end", "duration", "status", "locked", "note", "synchronized_at", "external_ref", "external_note", "external_failed", "created_at", "updated_at", "running"}, {"data.id", "data.card_id", "data.activity_id", "data.user_id", "data.start", "data.end", "data.duration", "data.status", "data.locked", "data.note", "data.synchronized_at", "data.external_ref", "data.external_note", "data.external_failed", "data.created_at", "data.updated_at", "data.running"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded data1",{{"count", Int64.Type}, {"limit", Int64.Type}, {"offset", Int64.Type}, {"data.id", type text}, {"data.card_id", type text}, {"data.activity_id", type text}, {"data.user_id", type text}, {"data.start", type datetime}, {"data.end", type datetime}, {"data.duration", Int64.Type}, {"data.status", type text}, {"data.locked", type logical}, {"data.note", type any}, {"data.synchronized_at", type any}, {"data.external_ref", type any}, {"data.external_note", type any}, {"data.external_failed", type logical}, {"data.created_at", type datetime}, {"data.updated_at", type datetime}, {"data.running", type logical}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"offset", "data.id", "data.card_id", "data.activity_id", "data.user_id", "data.start", "data.end", "data.duration"})
in
#"Removed Other Columns"
