Azure DevOps OData Query Expand Column error

[prabhatnath]

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:

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

= 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

[ferryv · Accepted solution]

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

[RicardoV2]

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

[ferryv]

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 · Accepted solution]

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 · Accepted solution]

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