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:
- Each Source Query uses the Function.InvokeAfter( Source, #duration(0,0,0,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.
