none
Trying to pull information from Azure DevOps. I don't know how to modify the code RRS feed

  • Question

  • I am pretty new to PowerBI and even newer to Azure DevOps. I'm trying to figure out how to create a report that will show us estimate, actual, and remaining hours/effort by person/Team Member by sprint from a DevOps project. I found the following code for a Burndown by Tasks' Remaining Work in the Microsoft documentation. I understand the basics of it, and I think I need to modify the groupby part, but I don't know what to add. I can get it to connect to our test DevOps, and get some blank columns, but not pulling in a lot of data which makes me think we don't have our sprints set up or this might be for a project in scrum and this one is in agile. I'm not sure.

    I really appreciate any help.


    let
       Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItemSnapshot? "
            &"$apply=filter( "
                &"startswith(Area/AreaPath,'{project}') "
                &"and StateCategory ne 'Completed' "
                &"and DateValue ge Iteration/StartDate "
                &"and DateValue le Iteration/EndDate "
                &"and Iteration/StartDate le now()  "
                &"and Iteration/EndDate ge now() "
                &"and WorkItemType eq 'Task' "
            &") "
                &"/groupby( "
                &"(DateValue,State,WorkItemType,Activity,Priority,Area/AreaPath,Iteration/IterationPath,AreaSK), "
                &"aggregate($count as Count, RemainingWork with sum as TotalRemainingWork) "
            &") "
        ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4]) 
    in
        Source

    Friday, February 28, 2020 2:16 PM

Answers

  • This requires Sprint Start Date and End Date set up. Iteration/StartDate and Iteration/EndDate represents them.
    This might be the reason for the blank column. Can you update so and let us know if the query works fine ?
    FYI: the query will give data for the present sprint. Once this data could be fetched we can look further for each sprint
    Tuesday, March 17, 2020 9:03 AM