Showing results for 
Search instead for 
Did you mean: 
New Member

Using Power Query to Pull Web APIs. Error Cannot

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 =>
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.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"}, {"", ""}),
#"Expanded Column1.vendor" = Table.ExpandRecordColumn(#"Expanded Column1.lmo", "Column1.vendor", {"id", "name", "scac"}, {"", "", "Column1.vendor.scac"}),
#"Expanded Column1.district" = Table.ExpandRecordColumn(#"Expanded Column1.vendor", "Column1.district", {"id", "name"}, {"", ""})
#"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.product", "", "", "", "", "Column1.vendor.scac", "", "", "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.product", "", "", "", "", "Custom.Column1.vendor.scac", "", "", "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"}),

#"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. 

Memorable Member
Memorable Member

You can try adding an error handle to the 4th line in query 2:



#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each try Query1([Page]) otherwise null ),

Did I answer your question? Mark my post as a solution! Proud to be a Super User!

Connect with me!
Stay up to date on  
Read my blogs on  

Helpful resources



We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.


The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors