Create custom calculations with Power Query

VSTS

Note

Feature availability: The Analytics Marketplace extension is available to all VSTS accounts and provides access to several useful
widgets, such as Cumulative Flow, Velocity, and Lead and Cycle Time. The Analytics OData endpoint, which provides a concise model over the VSTS suite for Work Item Tracking, Test, Version Control, and
Build, is in a closed preview and only available to select VSTS customers at this time.

If you are looking for information about the Azure Analysis Services, see Azure Analysis Services or What is Azure Analysis Services?.

One of the benefits of using Power BI or Excel is the ability to add calculated fields to the data set. There are several ways to add a calculated column and you can use either Power Query or DAX.

In this topic we'll add a simple but useful column - an Age In Days column.

  1. First, navigate to the correct location within the file:

  2. Choose to Edit Queries:

    For Excel:

    In the Workbook Queries pane (if this is not visible, select the Data tab and Show Queries), right-click WorkItems and click Edit.

    Edit Queries

    For Power BI

    From the Home tab, click Edit Queries.

    Edit Queries

  3. Select the WorkItems query from the query list

  4. On the Add column tab, click Add Custom Column

    Add Custom Column

  5. Enter the calculation as shown in the following image:

    Custom calculation

    This formula simply calculates the duration between the current date and time and when the work item was created. It then turns the duration into days.

  6. Click OK then click the Home tab.

  7. Select the AgeInDays column and change the data type to Text.

    Change the column type to Text

    We need to do this otherwise the application tries to sum this value when applied to axis which does not produce the desired results

  8. Click Close & Apply

    Then you can create a chart similar to the one below which shows a histogram of open bugs by age in days.

    Figure 9