Forum Discussion
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:
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)/AreaCan you guide me on how can I expand the columns to get the required data?
Thanks,
Prabhat
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-devops&tabs=powerbi 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
SourceArea, 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-devops&tabs=powerbi as an example
4 Replies
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-devops&tabs=powerbi 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
SourceArea, 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-devops&tabs=powerbi as an example
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-devops-rest-7.1&tabs=HTTP
