Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
TariqArnabi
Frequent Visitor

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


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...


1 ACCEPTED SOLUTION
vaibhavkale570
Resolver III
Resolver III

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.

View solution in original post

1 REPLY 1
vaibhavkale570
Resolver III
Resolver III

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors