I have an issue with an Excel WebQuery to a NetSuite report timing out, it times out at about 90,000 records and there are 100,002 records in the report.
This is my working query that times out:
let
Source =
()=> Web.Page(Web.Contents("https://{redacted_account_num}.app.netsuite.com/app/reporting/webquery.nl?compid={redacted_compid}&entity=139057&email={redacted_email}&role=1189&cr=1030&hash={redacted_hash}")),
Delay = Function.InvokeAfter(Source, #duration(0,0,3,0)),
Data0 = Delay{0}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Document Number", type text}, {"Police QID", type text}, {"Police Role", type text}, {"Police Rank", type text}, {"Date Created", type date}, {"BAS SLA Info", type text}, {"Qty Shipped", type text}, {"BAS Category", type text}, {"Cooneen Size", type text}, {"Gender/Size/Length", type text}, {"Item", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","=","",Replacer.ReplaceText,{"Qty Shipped"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Qty Shipped", Int64.Type}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Document Number", "Police QID", "Police Role", "Police Rank", "Date Created", "BAS SLA Info", "BAS Category", "Cooneen Size", "Gender/Size/Length", "Item", "Qty Shipped"})
in
#"Reordered Columns"
This is my crude attempt at paginating through the results to attempt to extract smaller amounts of data at a time to avoid the time-out, my paginated code simply times out and returns nothing.
let
// Function to fetch a single page
FetchPage = (offset as number, limit as number) as table =>
let
Url = "https://{redacted_account_num}.app.netsuite.com/app/reporting/webquery.nl?compid={redacted_compid}&entity=139057&email={redacted_email}&role=1189&cr=1030&hash={redacted_hash}&limit=" & Text.From(limit) & "&offset=" & Text.From(offset),
Source = ()=> Web.Page(Web.Contents(Url)),
Delay = Function.InvokeAfter(Source, #duration(0,0,3,0)),
Data0 = Delay{0}[Data],
PromotedHeaders = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
ChangedType = Table.TransformColumnTypes(PromotedHeaders,{{"Document Number", type text}, {"Police QID", type text}, {"Police Role", type text}, {"Police Rank", type text}, {"Date Created", type date}, {"BAS SLA Info", type text}, {"Qty Shipped", type text}, {"BAS Category", type text}, {"Cooneen Size", type text}, {"Gender/Size/Length", type text}, {"Item", type text}}),
ReplacedValue = Table.ReplaceValue(ChangedType,"=","",Replacer.ReplaceText,{"Qty Shipped"}),
ChangedType1 = Table.TransformColumnTypes(ReplacedValue,{{"Qty Shipped", Int64.Type}})
in
ChangedType1,
// Pagination parameters
PAGE_SIZE = 1000, // Define the number of records per page
OFFSET_START = 0, // Starting offset
maxPages = 101, // Define the maximum number of pages to fetch
// Initialize the first page fetch
FinalTable = Table.Buffer(FetchPage(OFFSET_START, PAGE_SIZE)),
// Loop to fetch each page
Output = List.Generate(
()=> [Offset = OFFSET_START, Table = FinalTable],
each [Offset] < PAGE_SIZE * maxPages,
each [Offset = [Offset] + PAGE_SIZE, Table = Table.Combine({[Table], FetchPage([Offset], PAGE_SIZE) })]
),
// Get the final combined table
CombinedTable = Table.Combine(List.Transform(Output, each [Table])),
// Reorder columns in the combined table
ReorderedColumns = Table.ReorderColumns(CombinedTable, {"Document Number", "Police QID", "Police Role", "Police Rank", "Date Created", "BAS SLA Info", "BAS Category", "Cooneen Size", "Gender/Size/Length", "Item", "Qty Shipped"})
in
ReorderedColumns