Error when trying to refresh data

[orlando-noda]

Error when trying to refresh data

[orlando-noda]

Hi, @jenniferrosemitchell21! I had to edit the query of every table and add a filter parameter to limit my query to a specific areaPath. Here is an example of a table called "WorkItems Bl0cked"

Edited Query

= OData.Feed(#"Http/Https" & "://" & #"Analytics URL/Azure DevOps Server" & "/" & Organization & "/" & Project & "/_odata/v3.0-preview/WorkItemRevisions?" & "$apply=filter(" & "StateCategory ne 'Proposed'" & "and startswith(Area/AreaPath,'Systems\Opportunity')" & "and WorkItemType ne 'Issue'" & "and WorkItemType ne 'Task'" & "and WorkItemType ne 'Test Case'" & "and WorkItemType ne 'Test Plan'" & "and WorkItemType ne 'Shared Parameter'" & "and WorkItemType ne 'Shared Steps'" & "and WorkItemType ne 'Test Suite'" & "and WorkItemType ne 'Impediment'" & "and WorkItemType ne 'Epic'" & "and WorkItemType ne 'Feature'" & "and ChangedDate ge 2023-10-01T00:00:00Z" & ")", null, [Implementation="2.0", OmitValues=ODataOmitValues.Nulls, ODataVersion=4])

[nbrown02]

Hi there,

Thanks for sharing.

Is this a large ADO project? The error is one typically seen with a timeout due to a large dataset (I have several reports on a scheduled refresh not seeing any errors).

The workaround would be to change those tables and their queries to be a shorter date range...

[orlando-noda]

Yes, it is a huge project. They have over 50 teams on this project.

I've tried to change one query, but with no success.

Original query: = OData.Feed(#"Http/Https" & "://" & #"Analytics URL/Azure DevOps Server" & "/" & Organization & "/" & Project & "/_odata/v3.0-preview/WorkItemRevisions?" & "$apply=filter(" & "StateCategory ne 'Proposed'" & "and WorkItemType ne 'Issue'" & "and WorkItemType ne 'Task'" & "and WorkItemType ne 'Test Case'" & "and WorkItemType ne 'Test Plan'" & "and WorkItemType ne 'Shared Parameter'" & "and WorkItemType ne 'Shared Steps'" & "and WorkItemType ne 'Test Suite'" & "and WorkItemType ne 'Impediment'" & "and WorkItemType ne 'Epic'" & "and WorkItemType ne 'Feature'" & "and ChangedDate ge 2023-06-01T00:00:00Z" & ")", null, [Implementation="2.0", OmitValues=ODataOmitValues.Nulls, ODataVersion=4])

New Query: = OData.Feed(#"Http/Https" & "://" & #"Analytics URL/Azure DevOps Server" & "/" & Organization & "/" & Project & "/_odata/v3.0-preview/WorkItemRevisions?" & "$apply=filter(" & "StateCategory ne 'Proposed'" & "and Area Path eq 'Project\Systems\Opportunity to Cash'" & "and WorkItemType ne 'Issue'" & "and WorkItemType ne 'Task'" & "and WorkItemType ne 'Test Case'" & "and WorkItemType ne 'Test Plan'" & "and WorkItemType ne 'Shared Parameter'" & "and WorkItemType ne 'Shared Steps'" & "and WorkItemType ne 'Test Suite'" & "and WorkItemType ne 'Impediment'" & "and WorkItemType ne 'Epic'" & "and WorkItemType ne 'Feature'" & "and ChangedDate ge 2023-06-01T00:00:00Z" & ")", null, [Implementation="2.0", OmitValues=ODataOmitValues.Nulls, ODataVersion=4])

When I tried to change this query, got the following error:

ps: I'm not a developer, so maybe a simple detail that I'm missing.

[nbrown02]

So there is a thread in discussions on area path filtering - #33

I'd also try change the date range, as it is loading from June 1st (2023-06-01) it might make sense to start from say September (2023-09-01)

[orlando-noda]

Hi, @nbrown02 ! Just to let you know that this work around has worked to me.

Thank you for the support

[nbrown02]

Fantastic glad to hear it!On 10 Jan 2024, at 19:12, Orlando Noda ***@***.***> wrote: Hi, @nbrown02 ! Just to let you know that this work around has worked to me. Thank you for the support —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you were mentioned.Message ID: ***@***.***>

[jenniferrosemitchell21]

@orlando-noda Hi there, Would you mind sharing how you restricted your data? Which Query did you do this in? I've looked at the queries but none of them mirror your paste above.