Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
prabhatnath
Advocate III
Advocate III

Azure DevOps OData Query Expand Column error

Hi Friends,

 

I am trying to load Azure DevOps data into PBI using the below query and it loads data fine without any issues.

The problem I face is when I try to expand the columns like Area, Iteration, AssignedTo, and Tags.

let
   Source = OData.Feed ("https://analytics.dev.azure.com/{orgname}/{projname}/_odata/v4.0-preview/WorkItems?
    $select=WorkItemId,Title,State,WorkItemType,Area,Iteration,AssignedTo,Tags
    &$filter=startswith(Area/AreaPath,'Common\MyAreaPath')
    and WorkItemType eq 'Bug'"
     ,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
    Source

 
Output data looks like this:

prabhatnath_0-1690822968237.png

 

I can view the columns but when I expand I get the below error:

prabhatnath_1-1690823066648.png

= Table.ExpandRecordColumn(Source, "Area", {"AreaName"}, {"AreaName"})
DataSource.Error: OData: Request failed: The remote server returned an error: (400) Bad Request. (VS403483: The query specified in the URI is not valid: VS403489: The Analytics Service doesn't support key or property navigation like WorkItems(Id) or WorkItem(Id)/AssignedTo. If you getting that error in PowerBI, please, rewrite your query to avoid incorrect folding that causes N+1 problem..)
Details:
    DataSourceKind=OData
    DataSourcePath=https://analytics.dev.azure.com/orgName/ProjName/_odata/v4.0-preview/WorkItems(1555703)/Area
    Url=https://analytics.dev.azure.com/orgName/Projname/_odata/v4.0-preview/WorkItems(1555703)/Area

 Can you guide me on how can I expand the columns to get the required data?

 

Thanks,

Prabhat

2 ACCEPTED SOLUTIONS
ferryv
Resolver II
Resolver II

Area, Iteration and AssignedTo are expandable fields, so need to be added to the expand section of the query, not the select section. See also: https://learn.microsoft.com/en-us/azure/devops/report/powerbi/sample-boards-openbugs?view=azure-devo... as an example

 

let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?"
&"$filter=WorkItemType eq 'Bug' "
&"and StateCategory ne 'Completed' "
&"and startswith(Area/AreaPath,'{areapath}') "
&"&$select=WorkItemId,Title,WorkItemType,State,Priority,Severity,TagNames,AreaSK "
&"&$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath) "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source

View solution in original post

ferryv
Resolver II
Resolver II

Area, Iteration and AssignedTo are expandable fields, so need to be added to the expand section of the query, not select. See also: https://learn.microsoft.com/en-us/azure/devops/report/powerbi/sample-boards-openbugs?view=azure-devo... as an example

View solution in original post

4 REPLIES 4
RicardoV2
Frequent Visitor

Considering that, if I want to expand every column, what should i place in $select (or should i remove it)?

 

You should be able to remove the $select and for expand, you should be able to set it to $expand=all. See also: https://learn.microsoft.com/en-us/rest/api/azure/devops/wit/work-items/get-work-item?view=azure-devo...

ferryv
Resolver II
Resolver II

Area, Iteration and AssignedTo are expandable fields, so need to be added to the expand section of the query, not select. See also: https://learn.microsoft.com/en-us/azure/devops/report/powerbi/sample-boards-openbugs?view=azure-devo... as an example

ferryv
Resolver II
Resolver II

Area, Iteration and AssignedTo are expandable fields, so need to be added to the expand section of the query, not the select section. See also: https://learn.microsoft.com/en-us/azure/devops/report/powerbi/sample-boards-openbugs?view=azure-devo... as an example

 

let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/WorkItems?"
&"$filter=WorkItemType eq 'Bug' "
&"and StateCategory ne 'Completed' "
&"and startswith(Area/AreaPath,'{areapath}') "
&"&$select=WorkItemId,Title,WorkItemType,State,Priority,Severity,TagNames,AreaSK "
&"&$expand=AssignedTo($select=UserName),Iteration($select=IterationPath),Area($select=AreaPath) "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.