Skip to content

Latest commit

 

History

History
241 lines (145 loc) · 9.43 KB

transform-analytics-data-report-generation.md

File metadata and controls

241 lines (145 loc) · 9.43 KB
title titleSuffix description ms.subservice ms.custom ms.author author ms.topic monikerRange ms.date
Power BI data transformations
Azure DevOps
Learn how to expand columns and transform Analytics data in Power BI to support report generation.
azure-devops-analytics
powerbi
kaelli
KathrynEE
how-to
>= azure-devops-2019
12/05/2022

Transform Analytics data to generate Power BI reports

[!INCLUDE version-gt-eq-2019]

Expand Area Path, Iteration Path Expand Assigned To Expand Descendents Expand Links Expand Teams

Expand columns

The query returns several columns that you need to expand before you can use them in Power BI. Any entity pulled in using an OData $expand statement returns a record with potentially several fields. You need to expand the record to flatten the entity into its fields. Examples of such entities are: AssignedTo, Iteration, and Area.

After closing the Advanced Editor and while remaining in the Power Query Editor, select the expand button on the entities you need to flatten.

  1. Choose the expand button.

    [!div class="mx-imgBorder"] Expand an entity column, Power BI + OData

  2. Select the fields to flatten.

    [!div class="mx-imgBorder"] Select the fields to flatten.

  3. The table now contains entity field(s).

    [!div class="mx-imgBorder"] The table now contains entity field(s).

  4. Repeat steps 1 through 3 for all columns representing several fields. For work items, these typically include:

    • AssignedTo
    • AreaPath
    • IterationPath

Expand Descendants column

The Descendants column contains a table with two fields: State and TotalStoryPoints. Expand it.

  1. Choose the Expand button, and select the columns to report on:

    :::image type="content" source="media/transform-data/descendants-column-expand.png" alt-text="Screenshot of Power BI Descendants column. ":::

  2. Check all the columns and choose OK.

    :::image type="content" source="media/transform-data/expand-descendents-property.png" alt-text="Screenshot of Power BI Descendants column, expand options. ":::

  3. The Descendants entity is flattened to the selected columns:

    :::image type="content" source="media/transform-data/descendents-expanded-columns.png" alt-text="Screenshot of Power BI expanded Descendants column. ":::

Pivot Descendants.StateCategory column

  1. Select the 1Descendants.StateCategory1 column header to select it.

  2. Select Transform menu and then Pivot Column. :::image type="content" source="media/transform-data/transform-menu-pivot-column.png" alt-text="Transform menu, Pivot Column option.":::

  3. In the Pivot Column dialog, for Values select Descendants.TotalStoryPoints, and then press OK. Power BI creates a column for every StateCategory value.

    :::image type="content" source="media/transform-data/descendants-pivot-column-dialog.png" alt-text="Dialog of Pivot Column for Descendants.TotalStoryPoints column. ":::

Expand the Links column

  1. Select the expand button on the Links column.

    [!div class="mx-imgBorder"] Power BI + OData - expanding a Links column
    :::image type="content" source="media/transform-data/links-column-expand.png" alt-text="Screenshot of Power BI Links column, expand options. ":::

  2. Select all the fields to flatten.

    [!div class="mx-imgBorder"] Select all the fields to flatten.
    :::image type="content" source="media/transform-data/links-column-expand.png" alt-text="Screenshot of Power BI Links column, expand options. ":::

  3. Select the expand button on the Links.TargetWorkItem column.

    [!div class="mx-imgBorder"] Select the expand button on the Links.TargetWorkItem column. :::image type="content" source="media/transform-data/links-target-work-item-column-expand.png" alt-text="Screenshot of Power BI Links.TargetWorkItem column, expand options. ":::

  4. Select the fields of the Target Work Item to flatten.

    [!div class="mx-imgBorder"] Select the fields of the Target Work Item to flatten.

    The Table now contains flattened Link and Target Work Item field(s).

    [!div class="mx-imgBorder"] The Table now contains flattened Link and Target Work Item field(s).

Note

If the link represents a one-to-many or many-to-many relationship, then multiple links will expand to multiple rows, one for each link.

For example, if Work Item #1 is linked to Work Item's #2 and #3, then when you expand the Links record, you will have 2 rows for Work Item #1. One that represents its link to Work Item #2, and another that represents its link to Work Item #3.

Transform a column data type

Transform the LeadTimeDays and CycleTimeDays columns to whole Numbers

The LeadTimeDays and CycleTimeDays are decimal fields. For example if Lead Time is 10 and 1/2 days, the value is 10.5. Since most Lead/Cycle Time reports assume that it's rounded to the nearest day, we need to convert these fields to an Integer. Making this conversion converts all values less than 1 to 0.

From the Power Query Editor, select the ribbon Transform menu.

  1. Select the LeadTimeDays column by selecting the column header.

  2. Select Data Type and change to Whole Numbers. :::image type="content" source="media/transform-data/change-data-type-lead-time.png" alt-text="Screenshot of Power BI Transform menu, Data type selection.":::

  3. Repeat for CycleTimeDays.

Change CompletedDateSK to a Date field

The CompletedDateSK column data corresponds to an integer rendering of the Completed Date field in the format YYYYMMDD. For example, the integer value of 2022-July-01 is 20220701. For easier reporting, we change it to a Date field.

From the Power Query Editor, select the ribbon Transform menu.

  1. Select the CompletedDateSK column header.

  2. Select Data Type and change to Text. When the Change Column Type dialog appears, select Add new step (rather than Replace current step). This two-step process is the easiest way to change it to a proper Date field in Power BI.

    :::image type="content" source="media/transform-data/change-column-type-add-new-step.png" alt-text="Screenshot of Power BI Transform menu, Change Column Type dialog.":::

  3. Next, select Date Type again and choose Date. In the Change Column Type dialog, select Add new step.

Replace Null values

Sometimes one or more records will contain null values. For example, a value may not have been entered for Story Points or Remaining Work.

:::image type="content" source="media/transform-data/records-null-data.png" alt-text="Screenshot of Power BI table containing null values.":::

For easier reporting, replace nulls with zero by following these steps.

  1. Select the column by clicking the column header.
  2. Select the Transform menu.
  3. Select Replace Values. In the Replace Values dialog:
    • Enter "null" in Value to Find.
    • Enter "0" in Replace With.
  4. Choose OK.

Create a custom column

Create a percentage complete computed column

  1. Select Add Column menu.

  2. Select Custom Column.

  3. Enter PercentComplete for New column name.

  4. Enter the following in Custom column formula.

    = [Completed]/([Proposed]+[InProgress]+[Resolved]+[Completed])

    :::image type="content" source="media/reports-boards/custom-column-dialog-percent-complete.png" alt-text="Custom Column Dialog, PercentComplete syntax.":::

    [!NOTE] It is possible you won't have a Resolved column, if the work items don't have States mapped to the Resolved State Category. If so, then omit "[Resolved]" in the above formula.

  5. Press OK.

  6. Select Transform menu.

  7. Select Data Type and select Percentage.

[!INCLUDE temp]

Rename column fields

When finished with your expansion, you may choose to rename one or more columns.

  1. Right-click a column header and select Rename...

    [!div class="mx-imgBorder"] Power BI Rename Columns

  2. Enter a new label for the column field and then press Enter.

Rename the query

  1. You can rename the query from the default Query1, to something more meaningful.

    [!div class="mx-imgBorder"] Power BI Rename Query

  2. Once done, choose Close & Apply to save the query and return to Power BI.

    [!div class="mx-imgBorder"] Power BI Close & Apply

TBD

  • Create custom fields - such as a percentage
  • Can't pivot if you have unexpanded records - will get an error message about nested columns
  • Must remove Null fields - use Replace values (why does this not work in some instances)