An example of how to use the FourJaw API with Microsoft Power BI.
Please ensure you have completed the first guide to gain an API key and access the interactive API docs before beginning this guide.
Introduction
Power BI has emerged as a useful tool for building dashboards and assembling data from across your business into one place. For this reason, we've been asked a lot whether the FourJaw data can be pulled into Power BI - in this guide, we'll show it can.
What we'll be building
We'll build a Power BI dashboard that pulls live data from the API into a bar chart showing utilisation per day over the last 14 days.
Request the data through Power Query
First, we'll create a blank new Power BI file which will launch to the report page. Then, we'll open the Power Query editor by clicking the "Transform data" button in the top nav bar.
The Power Query editor will open and the page will look rather empty. We'll add a new data source by selecting New Source > Blank Query.
A blank query will appear and we'll rename it as "14 Day Utilisation" by right clicking the name in the lefthand panel.
Now it's time to enter the code to request utilisation data from the API over the last 14 days. To do this, we'll open the Advanced Editor by clicking the "Advanced Editor" button at the top.
The approach here is to use Power Query code to do all the heavy lifting for us. This might seem intimidating, but is actually quite straightforward once you've become familiar with it.
Please note we'll not go into too much detail on the basics of API requests here, but you can always refer back to our API Request Fundamentals article if you would like to.
The code we're about to use builds up a URL with all the correct parameters, adds the authentication header with your API key, and then makes a request. You'll see that the start and end timestamp are dynamically calculated based upon the current time, so they always fetch the last 14 days worth of data.
In the Advanced Editor window, delete the default contents to give you an entirely blank text box. Then, please paste in the following code:
You'll need to make three changes:
1. Replace "staging10" with your subdomain in the BaseURL
2. Replace "657871d62d2a73c177275c1c" with your own asset ID in ApiURL
3. Replace "your_api_key_here" with your own API key
Once you've made these changes, close the Advanced Editor window by clicking "Done". If all is good, you should see a valid response come back from the API.
Filter the response
Now we have a valid response, there are a couple of filtering steps we need to do.
First, select the cell saying "List" and right click > Drill Down.
In the resulting page, click "To Table" in the top left.
Accept the default parameters in the resulting pop up by clicking "OK".
The next step is to expand the records into two columns using the arrows at the top of the column.
I like to rename the columns into something more human-friendly. For example, "Timestamp" and "Utilisation (%)". You can do this by right clicking the column header.
An important final step is to change the data type of the "Timestamp" column so Power BI treats it as time data. If we don't do this, it will limit our possibilities later on.
You should also mark the "Utilisation %" column as a "Decimal Number" data type for the same reason.
We've now completed our filtering - click "Close & Apply" in the top left.
You will be taken back to the reporting view and can see your "14 Day Utilisation" data on the right hand side.
Build the bar chart
In the report view, choose a "stacked column chart" from the menu.
Select the empty chart on the page so it has the 8 little tabs around the outside...
…and then tick the "Timestamp" and "Utilisation %" tick boxes in the rightmost panel.
By default it will show a single bar that sums up the utilisation from across all 14 days - this is not what we want! This can easily be fixed using the "X-axis" controls on the right.
Click the dropdown next to "Timestamp" and move the selection from "Date Hierarchy" to "Timestamp".
You should now see a plot with 14 days of valid utilisation data in, pulled from the API.
Adjusting how it looks
From a data perspective, it's all there but this is a rather ugly plot. Let's make the following transformations:
1: Resize the plot to be bigger and move it to the right of the page
2: Change the title to "Utilisation (Last 14 Days)" and make the font size larger
3: Rename the Y-Axis title to "Utilisation %" and make the font bigger
4: Make the font bigger on the X-axis title
5: Change the colour to FourJaw purple (hex code #6D2DD4)
That's a bit better.
Refreshing the data
There are several mechanisms in Power BI for publishing and refreshing data which are beyond the scope of this guide. However, the simplest way is to press "Refresh" at the top of the page.
Congratulations! You're all set to go further with Power BI now you've got a first example under your belt.
Feel free to get in touch if you have any questions or comments.