Introduction

The TIQK Developer API enables you to create TIQK-powered apps beyond our imagination. 

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

  • Active TIQK subscription
  • A TIQK API Key and API Secret, generated at tiqk.io under Your organisation > Integrations by your TIQK Administrator
  • For this example integration, you'll need the Microsoft PowerBI Desktop app for Windows installed, and an active PowerBI subscription. You can also use recent versions of Microsoft Excel for Windows, with differences noted below.
  • You'll find a copy of the latest sample PowerQuery script used below in our public GitHub repository.

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. 

Important: 

  • 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.


Sample TIQK Power Query M script

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

Did this answer your question?