Dataflow Timeout and "Query Contrains Errors"

[Anonymous]

Hey, I've set up a new gateway to access our data through an api / Gateway and want to set it up as a dataflow. 

I'm using a query which works in powerbi desktop , it takes about 10-15 minutes to refresh for 43k lines, but when I try to validate the query when setting up the dataflow it says. "The operation is timed out" and "The query contains errors". I have also tried pulling only 4k lines of data and get the same problem. 

Any help would be really appreciated

let
Source = Json.Document(Web.Contents("https://jira.corp.company.com/rest/api/2/search?jql=JIRAQUERY")),
BaseUrl = "https://jira.corp.company.com/rest/api/2/search?jql=JIRAQUERY",
EntitiesPerPage = 1000,
GetJson = (Url) =>
let RawData = Web.Contents(Url),
Json = Json.Document(RawData)
in Json,
GetTotalEntities = () =>
let Json = GetJson(BaseUrl),
Total = Json[total]
in Total,
GetPage = (Index) =>
let Skip = "startAt=" & Text.From(Index * EntitiesPerPage),
Url = BaseUrl & "&" & Skip & "&maxResults=" & Text.From(EntitiesPerPage),
Json = GetJson(Url),
Value = Json[issues]
in Value,
GetUrl = (Index) =>
let Skip = "startAt=" & Text.From(Index * EntitiesPerPage),
Url = BaseUrl & "&" & Skip & "&maxResults=" & Text.From(EntitiesPerPage)
in Url,
EntityCount = List.Max({ EntitiesPerPage, GetTotalEntities() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
URLs = List.Transform(PageIndices, each GetUrl(_)),
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
#"Converted to Table" = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

[Anonymous]

HI @Anonymous ,

It seems like you are using dynamic custom functions about iterator and pagination results that power bi dataflow does not support. I'd like to suggest you take a look at the following link to know more about the limits of using dataflow:

Creating and using dataflows in Power BI - Considerations and limitations 
Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hi @Anonymous ,

Thanks for the response. I was over thinking it. 
I used Relative path to get past the dynamic URL issue and used startAt and maxResults for each entity. ie rows from 0-2000 and then a second entity of 2001-4000 and so forth. 
Combining these tables into 1 made there no need for pagination.

Hope that helps someone that needs pagination in their dataflow. 

[JoeRobert06]

This is a good post however what if pagination is required when you are dealing with many pages. Is there a work around this? I am doing something similiar by using a relative path with a dataflow but embedding a function in the query so that I can merge all the pages together. This works in desktop but I cann get this to execution as a dataflow. Each row value comes back as an [Error] instead of a [list]