Retrieve Historical cut of Work Items from DevOps into PowerBI via Power Query

[TariqArnabi]

I'm looking for a solution for connecting Azure DevOps to Power BI to track historical changes for all work items within a project. This will allow the user to see historical snapshots of a single work item (i.e. any changes within 1 work item).  I tried utilizing a WIQL (Work Item Query Language) query  to retrieve a list of all work item IDs in the project and the Azure DevOps REST APIs "Updates" to fetch a list of updates made to a work item, including changes to field values, relations, links, attachments, and comments.  However there are limitations in regards to Power BI's "Web" source option and the raw JSON format of the Azure DevOps REST APIs that is coming in is throwing an error :
DataFormat.Error: We found extra characters at the end of the JSON input.
Details:
Value=<
Position=4

I've looked into using OData queries in pulling data into Power BI, But I have not been able to find a guide that will allow me tro track every change within a workitem in the current sprint.  
https://learn.microsoft.com/en-us/azure/devops/report/powerbi/sample-odata-overview?view=azure-devop...

[vaibhavkale570 · Accepted solution]

Hello,

To track historical changes for all work items within an Azure DevOps project and visualize them in Power BI, you can follow an alternative approach using Azure DevOps Analytics extension and Azure DevOps OData API to pull the data into Power BI. Here are the steps to achieve this:

1. Install the Azure DevOps Analytics extension: This extension allows you to connect to Azure DevOps data and provides a more structured and user-friendly way to query the data compared to the raw JSON format from the REST APIs.

2. Enable Analytics for your organization: Once the extension is installed, you need to enable Analytics for your Azure DevOps organization. Go to Project Settings > Boards > Queries > Analytics Views, and then create the Analytics views you need, like a view for tracking changes in work items.

3. Write an OData query to fetch historical changes: Using OData queries, you can fetch the historical changes for work items, including field value changes, relations, links, attachments, and comments. OData queries provide more flexibility and control over the data you want to retrieve.

4. Import data into Power BI: In Power BI, use the OData connector to import the data from the Azure DevOps Analytics endpoint. Power BI supports OData queries and can handle the historical data from work items.

5. Transform and visualize data: Once the data is imported into Power BI, you can transform it as needed and create visualizations to show historical snapshots of a single work item. You can build charts, tables, and other visualizations to represent the changes over time effectively.

6. Schedule data refresh: Since the data in Power BI is connected to the Azure DevOps OData API, you can schedule a periodic data refresh in Power BI to keep your reports up-to-date with the latest changes in Azure DevOps work items.

It's important to note that the specifics of the OData queries will depend on the exact data you want to retrieve, including filtering, sorting, and grouping. You may need to experiment with the queries to get the data in the desired format.

By using Azure DevOps Analytics and OData queries, you can overcome the limitations you faced with the raw JSON format and the "Web" source option in Power BI. This approach provides a more robust and scalable solution to track historical changes in work items and create insightful reports in Power BI.