The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to 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
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
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...
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
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
User | Count |
---|---|
87 | |
84 | |
36 | |
35 | |
32 |
User | Count |
---|---|
96 | |
75 | |
67 | |
52 | |
52 |