Use Process Intelligence Lens data in Power BI

In this blog post I'd like to show how you can use the data submitted by Hawkeye beacons to create meaningful Power BI dashboards. The emphasis being on pivoting the table data to use the beacon properties as columns.

Before you create a Nintex Hawkeye Process Lens, you need to add some beacons to the processes you want to analyse. These process beacons will log additional data and properties about the item based on what business rules were applied by the workflow. In my example, I transmit the following properties using workflow beacons:

Buyer Name
City
Country
Purchase Classification
Gender
Purchase Amount Requested
Discount Percentage
Discount Amount
Discount Applied
Approval Status

Once my workflow is running, Nintex records all this process beacon data and allows me to create a process lens in Hawkeye and export the Power BI template to Power BI Desktop. For more information on how to do this, see Dan Stoll's blog post on Getting Started with a Nintex Hawkeye Process Intelligence Lens.

Okay, so now that the Process Lens template has been opened in Power BI, I want to start creating a dashboard. The beacon data is stored in the table called ProcessBeacons and the two columns to look at are PropertyName and PropertyValue (ActivityID is the unique identifier for the item it relates to). You can view the table by selecting the relationships icon to the left of the screen. This is what the table looks like:

Now lets look at the table data. On the left of the Power BI interface, select the data icon. It will look something like this:

Oh dear... the PropertyName field contains all the columns I want to filter on and the PropertyValue contains all the row data! What now?

How to convert the PropertyName values to columns and set the PropertyValue values as row data

In Power BI Desktop, click and select the Home Tab > Edit Queries. This will open up the Query Editor.
In the explorer on the left, right-click on the ProcessBeacons table and select "Duplicate". This will create an exact copy of the table and its content. Rename the table.
TFor our new table we will need a unique identifier to know which properties belong to which items, the column names and the column values. So we will keep the ActivityID, PropertyName and PropertyValue columns and delete all other unnecessary columns. The table will now look like this:
Now all that is left is to select the PropertyName column, click and select the Transform tab > Piviot Column. Choose "PropertyValue" in the Values Column and ensure you open the Advanced Options and set the Aggregate Value Function to "Don't Aggregate".
Click OK and voila! Your table is now ready!
To save your changes, click on the Home tab > Close & Apply.