API Pagination + Throttling + Dynamic data source issue

[Landcrab]

Hi there,

Where to start... 🙂 (Said laughing and crying at the same time..help..please!!)

I want to create a sales report using the DEAR Inventory API v2: https://dearinventory.docs.apiary.io/#introduction/api-introduction

I can succefully load the data into Power Query/ Power BI desktop and refresh the data there, but, as soon as the report is published to Power BI service the dataset can nolonger be freshed and I get the error message:

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

Also in Power BI Desktop under Data Source Settings I see the warning:

'Some data sources may not be listed because of hand-authored queries.

In addition to that I have confirmed with DEAR Inventory that their API is throttled to only accept a maximum 60 API calls per minute and if thats exceeded it throws a 5xx error, hence the reason I have done the following:

1. Each Source Query uses the Function.InvokeAfter( Source, #duration(0,0,0,2) )
2. Parallel loading of tables has been disabled in Power BI Desktop under File > Options and Settings > Options > Current File > Data Load > 'Enable parallel loading of tables' = not ticked

After doing this, throttling is nolonger an issue in Power BI Desktop and Power BI Service when refreshing the data.

Steps I've taken that produce my current conundrum:

Step 1
Make a call to SaleList API endpoint to get list of SaleID's (header level details for each order)

SaleList https://dearinventory.docs.apiary.io/#reference/sale/sale-list

Source Query used:

let
// Converted to function to allow for use of Function.InvokeAfter thereby restricting API calls to less then 60 per minute
InitialFunction = () => Json.Document(Web.Contents("https://inventory.dearsystems.com/externalapi/v2/salelist", [Headers=[#"api-auth-accountid"="xxxxxxxxxxxxxxxxxxxxxxxx", #"api-auth-applicationkey"="xxxxxxxxxxxxxxxxxxxxxxxx"]])),
DelayedInvokeCall = Function.InvokeAfter(InitialFunction, #duration(0,0,0,2)),
#"Converted to Table" = Record.ToTable(DelayedInvokeCall),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "SaleList")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),
#"Expanded Value" = Table.ExpandListColumn(#"Removed Columns", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"SaleID", "OrderNumber", "Status", "InvoiceDate", "Customer", "CustomerID", "InvoiceNumber", "OrderLocationID"}, {"SaleID", "OrderNumber", "Status", "InvoiceDate", "Customer", "CustomerID", "InvoiceNumber", "OrderLocationID"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Value1", each ([Status] <> "VOIDED")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"SaleID"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"SaleID", type text}})
// Remove all columns except SaleID, ready for next step to come.
in
#"Changed Type"

Step 2

Create a new parameter as text containing one of the SaleID's retrieved from step 1 and use that as a part parameterized call for the next API endpoint, that retrieves Sale Order line level details (SKU number, Quantity) for each SaleID.

InitialSaleID = 1234abcd (as text datatype)

let
Source = (InitialSalesID as text) => let
// Converted to function to allow for use of Function.InvokeAfter thereby restricting API calls to less then 60 per minute
InitialFunction = () => Json.Document(Web.Contents("https://inventory.dearsystems.com/externalapi/v2/sale/invoice?SaleID=" & InitialSaleID, [Headers=[#"api-auth-accountid"="xxxxxxxxxxxxxxxxxxxxxxxxxx", #"api-auth-applicationkey"="xxxxxxxxxxxxxxxxxxxxxxxxxx"]])),
DelayedInvokeCall = Function.InvokeAfter(InitialFunction,#duration(0,0,0,2)),
#"Converted to Table" = Record.ToTable(DelayedInvokeCall),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "Invoices")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),
#"Expanded Value" = Table.ExpandListColumn(#"Removed Columns", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"Lines"}, {"Lines"}),
#"Expanded Lines" = Table.ExpandListColumn(#"Expanded Value1", "Lines"),
#"Expanded Lines1" = Table.ExpandRecordColumn(#"Expanded Lines", "Lines", {"ProductID", "SKU", "Name", "Quantity", "Price"}, {"ProductID", "SKU", "Name", "Quantity", "Price"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Lines1",{{"Price", Currency.Type}, {"Quantity", type number}, {"Name", type text}, {"SKU", type text}, {"ProductID", type text}})
in
#"Changed Type"
in
Source

Whilst before mentioned Steps 1 and 2 work in Power BI Desktop they do not deal with pagination and therefore do not retrieve all the sale-list data. Basically Step 1 calls sale-list endpoint but it only retrieve the first 100 rows and I therefore I need to add page number to the URL.

To deal with pagination I have tried the approach of modifying the Step 1 (sale-list API call) in accordance with Imke Feldmann's (@ImkeF) suggested video from here https://community.powerbi.com/t5/Desktop/Rest-API-Json-several-pages-automatically-call-the-next-pag...

Step 1 becomes:

(page as number) as table =>

let
// Max number of records that can be retrieved per API call is 1000 therefore adding this at the end of API string to limit number of pages that need to be retrieved
Source = Json.Document(Web.Contents("https://inventory.dearsystems.com/externalapi/v2/salelist?Page="& Number.ToText(page) &"&limit=1000", [Headers=[#"api-auth-accountid"="xxxxxxxxxxxxxxxxxxxxxxxxxxx", #"api-auth-applicationkey"="xxxxxxxxxxxxxxxxxxxxxxxxxxx"]])),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{2}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "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"

This works approach works in Power Query to retrieve all the pages, i.e. deal with pagination, but, once published to Power BI Service the dataset can nolonger be refreshed as it contains one or more dynamic sources as per first mentioned issue.

I have also tried this other approach suggested by Stacia Varga (@Stacia) but that too results in the published report not being able to be refreshed in Power BI Service due to dynamic sources used in dataset.

http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/

So the question is, how can I deal with throttling, pagination and avoid dynamic sources being used so I end up with a published report in Power BI service that can be refreshed??

Any help is greatly appreciated, thank you.

[Stacia]

I'm not sure what you tried based on my blog post, but hopefully I can point you in the right direction. The Web.Contents function wants a static URL in order to work properly in the Power BI service. You use the Query argument to override the static portion of the URL with the variable(s) that you want to use. Basically, the way that I think about it is to put everything that follows the ? in your static URL into the Query argument. 

I can't test it myself, but you're looking for something like this (check the parentheses, brackets, commas, etc.):

Source = Json.Document(Web.Contents(“https://inventory.dearsystems.com/externalapi/v2/salelist?Page=1&limit=1000”,

[Headers=[#"api-auth-accountid"="xxxxxxxxxxxxxxxxxxxxxxxxxxx", #"api-auth-applicationkey"="xxxxxxxxxxxxxxxxxxxxxxxxxxx”]], 

[Query = [Page=Number.ToText(page), limit=1000]]))

[Landcrab]

Thank you @Stacia, I had the square bracket in the wrong place. turns our it shoud be 

Source = Json.Document(Web.Contents(“https://inventory.dearsystems.com",[RelativePath="/externalapi/v2/salelist,Query=[Page=Number.ToText...,

Headers=[#"api-auth-accountid"="xxxxxxxxxxxxxxxxxxxxxxxxxxx", #"api-auth-applicationkey"="xxxxxxxxxxxxxxxxxxxxxxxxxxx”]]))

[blopez11]

I believe the service doesn't like the first parameter, url, to web.contents to be dynamic

I think your issue is below:

"https://inventory.dearsystems.com/externalapi/v2/salelist?Page="& Number.ToText(page) &"&limit=1000"

Try using the Query option of web.contents to set your parameters (Page, limit)

[Landcrab]

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"

[blopez11 · Accepted solution]

Take a look at this blog for how to use the query option available in web.contents

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

[Landcrab]

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...,

Headers=[#"api-auth-accountid"="xxxxxxxxxxxxxxxxxxxxxxxxxxx", #"api-auth-applicationkey"="xxxxxxxxxxxxxxxxxxxxxxxxxxx”]]))