Create custom calculations with Power Query

Last Update: 9/26/2016

Team Services

NOTE

Feature availability: The Analytics Service is in private preview and only available to select customers of Visual Studio Team Services at this time.

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