Getting data from VSTS using PowerBI and oData

[jonathan-levy]

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

[v-huizhn-msft]

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]

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