Steps to Make the Area and Iteration Path Easier to View
In some Azure DevOps projects we see long string values for Iteration Path due to nesting of iterations under common parent iteration. This long string value is not friendly for usage in Power BI visuals, such as bar graphs or table. This blog post shows you how to shorten the Area Path and Iterations by replacing the long or repetitive string values in a dataset.
The goal is the show the second level area path node. This removes the word AzureDevOps from the path.
Remove the First Node in the Area Path:
Open the Power Query editor by selecting the Transform Data icon on the menu bar
Select the Area Path column and Duplicate
Rename the new column to Area Path Short
Select Transform -> Extract -> Text after Delimiter
Expand the Advanced option
Enter a backslash for the Delimiter
Enter a 0 in the number of delimiters to skip
NOTE: To get the second level node in the Area Path to be the first value in the string, select 0. If you want the third node to be the first, then enter 1. This skips the first two nodes.
Close the wizard the values in the column are updated.
TO MAKE CHANGES or REVERT VALUES:
If the output from the action is not what you expected, you can make changes to the step in the Applied Step column.
Select the action in the Applied Steps and either right-click and select Edit or select the Gear icon. This opens the wizard
You can make a change to the number of delimiters to skip to pull in another node value. Select Ok.
If you want to remove the step, either right-click and select Delete. Or select the X icon on the left to remove the step
Select Close and Apply to update the data in the report.
Obtain a Node in the Area Path or Iterations
These steps show you how to obtain the node in the Area Path or Iteration out of the entire path. I want to obtain the second node in the Area Path as shown below.
Duplicate the Area Path or Iteration and rename the column.
Select the Extract Text Between Delimiters option on the menu bar. Enter the following values in the dialog:
Play with the Number of start delimiters to skip option to find the correct node in the path.
Select Close and Apply to save the values in the column.
Here is a blog on extracting the work item’s HTML or long text field used in the Description and other text boxes. This will amplify your report!