Modifying the Analytics View’s Data to Report Visual Friendly
Making the default data types from an Analytics View graph friendly means changing to a more readable format. The default data types for the fields in the analytics view are appropriate. There are a few fields I normally change for the report design process using the Power Query Editor before building calculations or reports.
Select the Home button on the menu bar and Transform Data. This opens the Power Query Editor. Become familiar with this editor as it will become your friend when creating more complex reports.
There are so many features in the Power Query Editor and there are plenty of great help articles and blog posts on how to use this editor. I will not be going into detail in this blog post.
Formatting the Date Fields
The default data type for all dates in the analytic view is Time Zone and this defaults to UTC time. You do not need this level of detail in a basic work item report. Change the data type to Date.
Select the Date columns (select the first one and hold down the ctrl button to select the others) and right click and select Change Type -> Date
Don’t worry if you make a mistake. You can undo any transformation by selecting the step in the Applied Steps section and selecting the X next to the step name. This will revert the data to its original format.
Creating a Searchable Work Item ID
I have created reports where customers needed to search on a work item ID to quickly view the data in the report. Otherwise, the user must scroll through the slicer to get to the work item to apply the filter. The Power BI slicer will only allow searching on values with a text data type. The default data type for a work item is Whole Number. This will not work for searching.
To create a searchable work item ID, select the Work Item ID field and select Duplicate Column. The new column is added to the very end of the field list.
Right click on the new column and select Change Type -> Text
Rename the column to Work Item ID Search. Right click on the column, select Rename and enter the new column name.
Select close and apply to load the transformed data into the report.
Here is a link to the steps to shorten the Area or Iteration Path fields in an Analytics View.