PowerBI & Excel integration

The API opens up a world of potential TIQK-powered integrations, including custom visualisations, dashboards, and analysis using the tools you love like Excel, PowerBI, and Tableau.

This article provides an example of how to create a Microsoft Power Query script to securely access and use your organisation's live TIQK audit results in a Microsoft PowerBI dashboard.

 

About Microsoft Power Query

Power Query (also known as Get and Transform in Excel 2016) provides a point-and-click interface to connect, combine, and refine data sources for analysis in Microsoft Excel and PowerBI.

Power Query scripts are written in the M language.

While this example uses PowerBI, the method and script for accessing live TIQK data in Excel is similar.

Pre-requisites

You'll find a copy of the latest sample PowerQuery script shown in this article in our public GitHub repository.

Connect to live TIQK data

Step 1: Add the Power Query script

Create a new, empty PowerBI file and choose Blank Query from the Get Data toolbar item:

 

If you're using Excel, choose the same Blank Query option from the Get Data > Other Sources item on the Data toolbar:


On the Query Editor screen that appears, name your query on the right hand side (e.g. "getFilesInTeamFolder"), and then click the Advanced editor toolbar item:

Paste the text from our sample Power Query script into the pop-up.

You'll find a copy of the latest sample PowerQuery script in our public GitHub repository.

This query script performs a few tasks: obtains an authorisation token using your API Key and Secret; uses that token to obtain a list of files and folders; and drills down into a specific folder to obtain the file list and file metadata inside. Comments are provided in the script itself.

Replace the text <Your API Key> and <Your API Secret> with the actual values from your account.

For security we do not recommended that you include your production API Key and Secret inside the script itself in a production environment.

It is only done here to simplify the example.

A better solution is to use Query Parameters for your APIKey and APISecret. If you use Parameters, you can pass them in using the format #"Parameter name" in the Advanced Query editor - see this discussion for more information

This sample script uses the production API URL (https://public-api.tiqk.io). Any audits completed under your account via the public API URL will count towards your usage and may incur additional subscription fees. For development and testing we offer a sandbox API URL with test limits. Contact our Customer Success team using the Chat bubble for more information.

If there are no syntax errors detected, click the Done button.

Step 2: Authorise the data connection

You may be prompted with a data privacy message:

To keep this example simple, we will ignore the privacy settings for this connection. In a production environment you should select an appropriate setting to ensure that your users can access the data from the Power Query in a secure manner.

Step 3: Preview the live results

If successful, the query will execute and the results (in this case, a list of files uploaded to your TIQK account, and their meta data including any overall audit results) will be previewed:

Step 4: Close and apply the query

When ready, click Close & Apply on the toolbar to save the Query, and load the live results into the PowerBI file or Excel workbook for further analysis or visualisation in your custom dashboard:

To refresh the dashboard or Excel workbook with the latest data from the TIQK platform, choose the Home > Refresh (or Data > Refresh in Excel) button.