Add a Work Item’s Long Text Field Data to an Analytics View for the Power BI Report

Adding Long Text and HTML Fields to Analytics Views and ODATA Queries in Power B

To add the HTML and long text fields to an existing Analytic View or ODATA query in the Power BI report, you need to add a custom function to the Power Query editor to return the values through the API. A simple custom function can be used to return the work items’ values and is easy to implement. Please note that the work item’s long text or HTML fields are not available by default in the Analytics Views or ODATA queries

Data Modeling:

The Work Item ID field’s data type is numeric by default. This data type cannot be passed to an API call and we need to change to a text data type. These steps will duplicate the Work Item ID column and change the data type.

Open the Power Query editor by selecting the Transform button on the menu bar.

Select the Work Item ID field and right click and select Duplicate Column. The column will appear at the end of the list. Right click on the column and select Change Type -> Text

Rename the column to WorkItemID-API.

Add the Custom Function:

Add a function to the Power BI report to execute the call to the Work Item API. Select New Source -> Blank Query.

The following M code accepts a work item ID as string or text data type and calls the API to return the work item’s value.

You need to change the ADO Organization and the Team Project.

(wiID as text) =>
let
    Source = VSTS.Contents("https://dev.azure.com/<ADO-Organization>/<TeamProject>/_apis/wit/workitems/" & wiID & "?api-version=6.0")
in
    Source

Test the API for ensure the syntax is correct. Enter a valid work item ID and select Invoke. Check to see the return result set is correct. Delete the result set after testing.

Add the API Code to an Existing Dataset:

This section explains the process to add the return values from the API to the existing table. This table is based on an Analytics View and could also be of the origin of an ODATA query or even another API call.

On the menu bar, select Add Column and then Invoke Custom Function.

Select the newly created function from the Function Query drop down.

Enter a column name in the New Column Name field. This will be the return value from the API and used to expand the columns in the table.

Select the wiID value from the drop down and the field, WorkITemID-API. This is the Work Item ID field we changed to a text data type and is used as the function parameter.

Select OK to load the result set.

Expand the Columns in the Table:

A successful result set returns a JSON record. Expand the columns by selecting the Expansion.

Select the Fields column.

Select the HTML or long text fields to add the table. Most of the other columns are available through either the analytics views or the ODATA query.

Rename the newly added columns.

Notice the values in the fields have markup. You will need to use an HTML reporting control in the Power BI report to properly view the data.

Select Close and Apply to load the data from the API into the Power BI result set.

The data is now ready for reporting. The next blog post shows how to create a pop up report to show the values in a properly formated view.