Your ADO organization probably generates a high number of work items and Power BI is an excellent reporting tool to help visual and track the work your team is assigned for an iteration. When creating Power BI reports using work items, it is important to be able to search for specific work items by the ID. However, by default, the work item data type is whole number or integer. The Power BI slicer only allows for searching for values if the data type is text. Users of the Power BI report may want to focus on a specific work item and providing them with a method to search by Work Item ID will enhance the user’s reporting experience.
The steps in this blog post show you how to change the data type of the Work Item to allow the users to search for a specific Work Item by the ID.
Data Transformation:
Create the Analytics View (AV) to retrieve the work items from Azure DevOps. Connect the AV to a Power BI Desktop report. This blog post explains the entire process.
Open Power Query editor to transfor the data.
Right click on Work Item ID field and select Duplicate Column
Right click on Work Item ID – Copy field and select Rename
Rename to WorkItemID-Search
Right click on WorkItemID-Search field and select Change Type
Select Text
Select Close and Apply to the Power Query window
Data will refresh
Power BI Report Update:
Add a slicer to the report canvas
Add the field WorkItemID-Search field to the slicer
Change the slicer type to drop down
Select the ellipse on the slicer
Select the Search option
Search is now enabled for the slicer!
