I am using Web.Contents to pull APIs. The website I am using only allows you to pull 1000 records at a time, so I have 2 querys one that is a table say 1-1000, and the the other downloads 1 page of 1000 records as a function (Page 1-1000).
This works fine as long as there is data on the JSON that is pulled in the query, but when it gets to the page where there is no longer data lets say page 200 it throws a code stating the column 1 cannot be found. I know this is because there no longer is a coulum 1 on the JSON document after page 200 but the query is pulling to page 1000. I hoped it would just pull a null value for everything after page 200, and the end result would be all the data availbe on the web API.
I would like to be able to pull all of the data every time it querys, but im not sure if this is the most effective method.
Query 1
(Page as number) as table =>
let
Source = Json.Document(Web.Contents("??????????????", [Query=[ #"product"="sand", #"page"=Number.ToText(Page)], Headers=[Authorization="?????????????"]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "product", "lmo", "vendor", "district", "createdTimestamp", "updatedTimestamp", "dispatchedTimestamp", "endTimestamp", "createByEmail", "logisticsStatus", "description", "purchaseOrderNumber", "purchaseOrderLineItemNumber", "bolNumber", "bulkStorageNumber", "ticketNumber", "salesOrderNumber", "jobName", "billing", "tasks", "invoiceStats"}, {"Column1.id", "Column1.product", "Column1.lmo", "Column1.vendor", "Column1.district", "Column1.createdTimestamp", "Column1.updatedTimestamp", "Column1.dispatchedTimestamp", "Column1.endTimestamp", "Column1.createByEmail", "Column1.logisticsStatus", "Column1.description", "Column1.purchaseOrderNumber", "Column1.purchaseOrderLineItemNumber", "Column1.bolNumber", "Column1.bulkStorageNumber", "Column1.ticketNumber", "Column1.salesOrderNumber", "Column1.jobName", "Column1.billing", "Column1.tasks", "Column1.invoiceStats"}),
#"Expanded Column1.lmo" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.lmo", {"id", "name"}, {"Column1.lmo.id", "Column1.lmo.name"}),
#"Expanded Column1.vendor" = Table.ExpandRecordColumn(#"Expanded Column1.lmo", "Column1.vendor", {"id", "name", "scac"}, {"Column1.vendor.id", "Column1.vendor.name", "Column1.vendor.scac"}),
#"Expanded Column1.district" = Table.ExpandRecordColumn(#"Expanded Column1.vendor", "Column1.district", {"id", "name"}, {"Column1.district.id", "Column1.district.name"})
in
#"Expanded Column1.district"
Query 2
Source = {1..1000},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Page"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each Query1([Page])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1.id", "Column1.product", "Column1.lmo.id", "Column1.lmo.name", "Column1.vendor.id", "Column1.vendor.name", "Column1.vendor.scac", "Column1.district.id", "Column1.district.name", "Column1.createdTimestamp", "Column1.updatedTimestamp", "Column1.dispatchedTimestamp", "Column1.endTimestamp", "Column1.createByEmail", "Column1.logisticsStatus", "Column1.description", "Column1.purchaseOrderNumber", "Column1.purchaseOrderLineItemNumber", "Column1.bolNumber", "Column1.bulkStorageNumber", "Column1.ticketNumber", "Column1.salesOrderNumber", "Column1.jobName", "Column1.billing", "Column1.tasks", "Column1.invoiceStats"}, {"Custom.Column1.id", "Custom.Column1.product", "Custom.Column1.lmo.id", "Custom.Column1.lmo.name", "Custom.Column1.vendor.id", "Custom.Column1.vendor.name", "Custom.Column1.vendor.scac", "Custom.Column1.district.id", "Custom.Column1.district.name", "Custom.Column1.createdTimestamp", "Custom.Column1.updatedTimestamp", "Custom.Column1.dispatchedTimestamp", "Custom.Column1.endTimestamp", "Custom.Column1.createByEmail", "Custom.Column1.logisticsStatus", "Custom.Column1.description", "Custom.Column1.purchaseOrderNumber", "Custom.Column1.purchaseOrderLineItemNumber", "Custom.Column1.bolNumber", "Custom.Column1.bulkStorageNumber", "Custom.Column1.ticketNumber", "Custom.Column1.salesOrderNumber", "Custom.Column1.jobName", "Custom.Column1.billing", "Custom.Column1.tasks", "Custom.Column1.invoiceStats"}),
#"Expanded Custom.Column1.billing" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom.Column1.billing", {"status", "currency", "loadedMileage", "totalPayloadCost", "totalLogisticsCost", "totalCost", "totalLoadWeight", "pushedBackToVendorReason", "pushedBackToVendorTimestamp", "logisticsCharges", "attachments"}, {"Custom.Column1.billing.status", "Custom.Column1.billing.currency", "Custom.Column1.billing.loadedMileage", "Custom.Column1.billing.totalPayloadCost", "Custom.Column1.billing.totalLogisticsCost", "Custom.Column1.billing.totalCost", "Custom.Column1.billing.totalLoadWeight", "Custom.Column1.billing.pushedBackToVendorReason", "Custom.Column1.billing.pushedBackToVendorTimestamp", "Custom.Column1.billing.logisticsCharges", "Custom.Column1.billing.attachments"}),
in
#"Replaced Value4"
I realize there is a lot of table conversions here, but I can assure you I stipped it down to the bare minimum, and I still got the same issue. The problem is in query 1 the part I highlighted. If I invoke the function it works up to a certain page number and then it throws an error.
If the query 2 looks wrong I deleted some things that are irrelevant to make it easier to look at.
Any help or direction on how to fix this or to do it another way would be greatly appriciated.
