Forum Discussion
API Pagination + Throttling + Dynamic data source issue
Take a look at this blog for how to use the query option available in web.contents
Hi blopez11 ,
Thank you for looking it over. Not sure what you mean by query options for Web.Contents? I have tried splitting the URL differently like so:
"https://inventory.dearsystems.com" & /externalapi/v2/salelist?Page="& Number.ToText(page) &"&limit=1000"
and tried using RelativePath like this:
"https://inventory.dearsystems.com", [RelativePath="/externalapi/v2/salelist?Page="& Number.ToText(page) &"&limit=1000"]
When I use the RelativePath version the call to the query that handles the pagination fails
Query that handles pagination:
let
Source = List.Generate ( ()=>
[Result= try GetData(1) otherwise null, Page =1 ],
each [Result] <> null,
each [Result = try GetData([Page]+1) otherwise null, Page = [Page]+1],
each [Result] ),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"SaleID", "OrderNumber", "Status", "OrderDate", "InvoiceDate", "Customer", "CustomerID", "InvoiceNumber", "CustomerReference", "InvoiceAmount", "PaidAmount", "InvoiceDueDate", "ShipBy", "BaseCurrency", "CustomerCurrency", "CreditNoteNumber", "Updated", "QuoteStatus", "OrderStatus", "CombinedPickingStatus", "CombinedPaymentStatus", "CombinedTrackingNumbers", "CombinedPackingStatus", "CombinedShippingStatus", "CombinedInvoiceStatus", "CreditNoteStatus", "FulFilmentStatus", "Type", "SourceChannel", "ExternalID", "OrderLocationID"}, {"SaleID", "OrderNumber", "Status", "OrderDate", "InvoiceDate", "Customer", "CustomerID", "InvoiceNumber", "CustomerReference", "InvoiceAmount", "PaidAmount", "InvoiceDueDate", "ShipBy", "BaseCurrency", "CustomerCurrency", "CreditNoteNumber", "Updated", "QuoteStatus", "OrderStatus", "CombinedPickingStatus", "CombinedPaymentStatus", "CombinedTrackingNumbers", "CombinedPackingStatus", "CombinedShippingStatus", "CombinedInvoiceStatus", "CreditNoteStatus", "FulFilmentStatus", "Type", "SourceChannel", "ExternalID", "OrderLocationID"})
in
#"Expanded Column1"
Take a look at this blog for how to use the query option available in web.contents
Thank you blopez11 for pointing me in the right direction, turns out I had the brackets in the wrong place. Ended up being:
Source = Json.Document(Web.Contents(“https://inventory.dearsystems.com",[RelativePath="/externalapi/v2/salelist,Query=[Number.ToText(page),limit=1000],
Headers=[#"api-auth-accountid"="xxxxxxxxxxxxxxxxxxxxxxxxxxx", #"api-auth-applicationkey"="xxxxxxxxxxxxxxxxxxxxxxxxxxx”]]))