Forum Discussion
How to load Azure Devops Boards data in Power BI?
I have been trying to load Board Column data from Azure Devops into Power BI but I am getting the following error when trying to expand the BoardsLocation field:
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..)
I am connecting using Odata and this is how I am doing it in a dataflow:
OData.Feed("https://analytics.dev.azure.com/{org}/{project}/_odata/v3.0-preview", null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
The above expands on other fields like WorkItems fine but it is throwing the above error and I cannot understand why.
3 Replies
Hi Anonymous,
It seems like the error is related to the query specified in the URI not being valid. The Analytics Service doesn't support key or property navigation like WorkItems(Id) or WorkItem(Id)/AssignedTo, which might be the reason why the BoardsLocation field is not expanding correctly.
Hi Sahir,
Thank you for your replies.
I think the API route will be very difficult to maintian and develop because I am trying to get historical work item revisions of the board columns for a huge amount of data. The API docs show that each API call retrieves the WorkItemRevisions of one WorkItem at a time.
Why is it that the data can be located in WorkItemRevisions (BoardLocations column), but it cannot be expanded? What is resulting in the N + 1 problem? I have even tried to expand the BoardLocations column for one WorkItem at a time and it still results in the same error.
It's recommended to rewrite the query to avoid the N+1 problem, which is causing the request to fail. You could try a different approach to retrieve the Board Column data, such as using the Azure DevOps REST API instead of OData.
