Hi all, I'm getting an error in a query that just started a few days ago after having worked flawlessly for a week or so. I am extracting data from HubSpot via API and loading to a table in Dataverse using a PowerQuery DataFlow. I am using an adaption of some code I have found on this and HubSpot's community forums, but again, it worked fine and has just stopped. The code and some comments are below:
let
headers = [
#"Content-Type"= "application/json",
Authorization="Bearer XXXXXXXXXXXXXXXXXXXXXX"
],
initContent = Text.ToBinary("
{
""filterGroups"": [
{
""filters"": [
{
""propertyName"": ""hs_lastmodifieddate"",
""operator"": ""GTE"",
""value"": " & Text.From(#"Last Update Deals") & " // Separate query that returns a Unix datetime stamp of last updated deal
},
{
""propertyName"": ""pipeline"",
""operator"": ""IN"",
""values"": [" & Text.From(#"Sales Pipelines") & "] // Separate query that returns only the pipelines I'm interested in
}
]
}
],
""properties"": [" & Text.From(#"Properties Deals") & "], // Separate query that returns all properties I need
""limit"": 100,
""after"": 0
}
"),
initReq = Web.Contents(
baseuri,
[
Headers= headers,
Content= initContent,
ManualStatusHandling= {400, 401, 403, 404, 500}, IsRetry = false
]
),
initData = Json.Document(initReq)[results],
// We want to get data = {lastNPagesData, thisPageData}, where each list has the limit # of Records,
// then we can List.Combine() the two lists on each iteration to aggregate all the records. We can then
// create a table from those records
gather = (data as list, nextContent) =>
let
//get new offset from active uri
newOffset = Json.Document(Web.Contents(baseuri,
[
Headers= headers,
Content = nextContent
]
)
)[paging][next][after],
//build new content using a copy of the initContent so we dont append offsests
newContent = Text.ToBinary("
{
""filterGroups"": [
{
""filters"": [
{
""propertyName"": ""hs_lastmodifieddate"",
""operator"": ""GTE"",
""value"": " & Text.From(#"Last Update Deals") & "
},
{
""propertyName"": ""pipeline"",
""operator"": ""IN"",
""values"": [" & Text.From(#"Sales Pipelines") & "]
}
]
}
],
""properties"": [" & Text.From(#"Properties Deals") & "],
""limit"": 100,
""after"": 0
}
"),
//get new req & data
newReq = Json.Document(Web.Contents(
baseuri,
[
Headers= headers,
Content= newContent
]
)
),
newdata = List.Buffer(newReq[results]),
//add that data to rolling aggregate. I suspect here is where I'm hitting performance issues resulting in the error (or a stack overflow error)
data = List.Combine({data, newdata}),
//if theres no next page of data, return. if there is, call @gather again to get more data
check = if Table.Contains(
Record.ToTable(newReq),
[Name = "paging"] ) = true
then @gather (List.Buffer(data), newContent) // I have read that buffering tables or lists could be helpful, but i have not found it so
else data
in check,
// before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
output = if Table.Contains(
Record.ToTable(Json.Document(initReq)),
[Name = "paging"]) = true
then gather(initData, initContent)
else initData,
#"Convert if List" = Table.FromList(output, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Convert if List", "Column1", {"id", "properties", "createdAt", "updatedAt"}, {"id", "properties", "createdAt", "updatedAt"})
in
#"Expanded Column1"
Any help at all would be very well appreciated. \
Cheers,
Ben
