Have search on a few sites, and any suggestions I am finding does not fit my situation. In many cases, such as how to export html table to excel, with pagination there are only limited responses. I am able to pull data using API key from a website, but it is paginated. I have been able to adjust my query to pull 100 records per page (default 25) and can input a page number to pull a select page, but have been unsuccessful in pulling as one table. Currently one of the data sets is over 800 records, so my work around is 8 queries, all pulling down a separate page, and then using the query amalgamate function to group into one table. I have a new project, that will likely return several thousand line items, and would prefer a simpler way to handle this.
This is current code
let
Source = Json.Document(Web.Contents("https://api.keeptruckin.com/v1/vehicles?access_token=xxxxxxxxxxxxxx&per_page=100&page_no=1", [Headers=[#"X-Api-Key"="f4f1f1f0-005b-4fbb-a525-3144ba89e1f2", #"Content-Type"="application/x-www-form-urlencoded"]])),
vehicles = Source[vehicles],#"Converted to Table" = Table.FromList(vehicles, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"vehicle"}, {"Column1.vehicle"}),
#"Expanded Column1.vehicle" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.vehicle", {"id", "company_id", "number", "status", "ifta", "vin", "make", "model", "year", "license_plate_state", "license_plate_number", "metric_units", "fuel_type", "prevent_auto_odometer_entry", "eld_device", "current_driver"}, {"Column1.vehicle.id", "Column1.vehicle.company_id", "Column1.vehicle.number", "Column1.vehicle.status", "Column1.vehicle.ifta", "Column1.vehicle.vin", "Column1.vehicle.make", "Column1.vehicle.model", "Column1.vehicle.year", "Column1.vehicle.license_plate_state", "Column1.vehicle.license_plate_number", "Column1.vehicle.metric_units", "Column1.vehicle.fuel_type", "Column1.vehicle.prevent_auto_odometer_entry", "Column1.vehicle.eld_device", "Column1.vehicle.current_driver"})
in #"Expanded Column1.vehicle"
