Forum Discussion

jonathan-levy's avatar
jonathan-levy
New Member
8 years ago

Getting data from VSTS using PowerBI and oData

Hi

 

I'm trying to create a burndown chart using Power BI with data from the WorkItemSnapshot. 2 questions regarding this URL: 

 

https://{url}.analytics.visualstudio.com/{project}/_odata/v1.0/WorkItemSnapshot?$apply=filter(WorkItemType eq 'Task')/filter(DateValue ge Iteration/StartDate and (Iteration/EndDate eq null or DateValue le Iteration/EndDate))/groupby((DateValue, IterationSK, SumOfRemainingWork), aggregate(RemainingWork with sum as SumOfRemainingWork, OriginalEstimate with sum as SumOfOriginalEstimate))&$orderby=DateValue

 

1 - Why do all the columns from WorkItemSnapshot show in Power BI whereas via the browser it only shows the 4 columns in the groupby section

 

2 - How can I get SumOfOriginalEstimate and SumOfRemainingWork to show in Power BI? 

 

Thanks

2 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    Hi jonathan-levy,

    After research, it seems that you need to query parameters to the URL of query part in OData.Feed function. In Power BI desktop, we connect the data using Power Query, you can post this case to Query Forum to get dedicated support. And I list some similar thread and articles, hope they are useful.

    Prefilter data for Power BI with OData URLs
    OData filter pass-through
    Project Online - Parameter in Excel OData Connection
    OData.Feed should push predicates on navigation properties to URL

    Best Regards,
    Angelia

    • jonathan-levy's avatar
      jonathan-levy
      New Member
      8 years ago

      Hi

       

      I'm trying to create a burndown chart using Power BI with data from the WorkItemSnapshot. 2 questions regarding this URL: 

       

      https://{url}.analytics.visualstudio.com/{project}/_odata/v1.0/WorkItemSnapshot?$apply=filter(WorkItemType eq 'Task')/filter(DateValue ge Iteration/StartDate and (Iteration/EndDate eq null or DateValue le Iteration/EndDate))/groupby((DateValue, IterationSK, SumOfRemainingWork), aggregate(RemainingWork with sum as SumOfRemainingWork, OriginalEstimate with sum as SumOfOriginalEstimate))&$orderby=DateValue

       

      1 - Why do all the columns from WorkItemSnapshot show in Power BI whereas via the browser it only shows the 4 columns in the groupby section

       

      2 - How can I get SumOfOriginalEstimate and SumOfRemainingWork to show in Power BI? 

       

      Thanks