Power BI limits OData.Query to WorkItemSnapshot in Azure DevOps Server by 10000 rows

Question

I'm making report about work progress in my company, how remaining/completed work is changed during time associated with given Epic work item.

I'm using:

• Azure DevOps Server 2019
• Power BI Desktop 64 bit Version: 2.65.5313.5141 64-bit (January 2019)
• OData query to select data from Azure DevOps Server

So I've found an issue.

• When I'm getting information about workitems through this element WorkItems:

let
    Tasks = OData.Feed("http://{localserver}:8080/tfs/{collName}/{projName}/_odata/v1.0/WorkItems?$apply=filter(WorkItemType eq 'Task')")
in
    Tasks 

I get all elements (65348 rows)

• When I'm getting information about workitems through this element WorkItemSnapshot:

let
    Tasks = OData.Feed("http://{localserver}:8080/tfs/{collName}/{projName}/_odata/v1.0/WorkItems?$apply=filter(WorkItemType eq 'Task')")
in
    Tasks 

I get only 10000 rows.

So, due to documentation, when using Power BI Desktop, PBI itself should load all paginated data. But as I see, it does not do it addressing WorkItemSnapshot model element.

How could I bypass this issue?

Answer 1

I've found reason of such behaviour:

VS403510: The Snapshot tables in Analytics are intended to be used only in an aggregation.  The result for this query has been reduced to preview of a single result page. Details on recommended query patterns are available here: https://go.microsoft.com/fwlink/?linkid=861060

So it is rather restriction of Azure DevOps Server