API: Integration With PowerBI

How to get data from the CyberPilot Report API into PowerBI

Note: In the current version of our Report API, you must first obtain an access token before using the API endpoints.

This makes integration with PowerBI cumbersome. It's something we will improve in the future by providing long lived access tokens, accessible directly from the CyberPilot platform.

This guide will explain how to get data from the CyberPilot Report API into PowerBI. It involves writing a function in PowerBI to fetch the access token, and writing a custom query to fetch the actual data.

In the future, this will all be possible through the PowerBI interface.

Let's get into it!

Part 1: Create a function to fetch an Access Token

With PowerBI open, click the Transform Data button:

Click New Source and select Blank Query

Then click Advanced editor:

In the text area that opens up, input the following code:

() => let body = "grant_type=password&client_id=<CLIENT_ID>&client_secret=<CLIENT_SECRET>&username=<USERNAME>&password=<PASSWORD>", Data = Json.Document(Web.Contents("https://login.app.cyberpilot.io/realms/app/protocol/openid-connect/token", [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content=Text.ToBinary(body)])), access_token = Data[access_token] 

in access_token

Make sure to replace <CLIENT_ID>, <CLIENT_SECRET>, <USERNAME> and <PASSWORD> with the credentials for your account.

Tip: The Client Secret and the Password can contain an ampersand (&). This is a reserved character when sending requests (which is what the function will do), and you will have to replace any ampersand with the URL encoded version of an ampersand, which is %26.

Next, click Done and rename this new function in the right hand side to i.e., GET Access Token:

Now you are ready to fetch data from the API. 😀👍

Part 2: Create a query

Like the previous step, create a New Source, select Blank Query and open the Advanced editor.

Insert the following code into the text area:

let 

    Source = Json.Document(Web.Contents("<URL>",  

        [Headers=[ 

            #"Authorization"="Bearer " & #"GET Access Token"(), 

            #"Content-Type"="application/json" 

        ]])) 

in 

    Source

Replace the <URL> in the code snippet above with the endpoint you want to fetch data from.

Click Done and wait for the editor the fetch the data.

Now, you can drill down and expand the data to extract the information you need:

Got a question?