Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dataflow Timeout and "Query Contrains Errors"

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"

 

 

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @v-shex-msft ,

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. 

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi @v-shex-msft ,

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. 

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]

 

JoeRobert06_0-1617198226262.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors