Select Section...

It is possible to import your SafetyCulture audit data into Power BI Desktop, where you can create useful visualisations of your data (see here).

Loading Data into Power BI Desktop

Export audit data using SafetyCulture Python SDK:

  1. Follow instructions here to export audit data as CSV using the SafetyCulture Python SDK
  2. Import the data into Power BI Desktop
  3. In Power BI Desktop, in the navigator pane, click Get Data.
  4. In Files, click Get
  5. Navigate and open the CSV file exported using the SafetyCulture CSV Export tool.

Note you can also import the CSV file directly to Power BI online:

Export audit data using the SafetyCulture Google Sheets Add-on

Note: This option requires Power BI Desktop (only available on Windows PC). Google Sheets cannot be connected directly with Power BI online.

  1. Follow instructions here to export your audit data to Google Sheets using the SafetyCulture Add-on
  2. Publish the Google Sheet to the web as an Excel file.  At the top of the Google Sheet, click File –> Publish to the web. Note that Published files update automatically whenever a change is made in the original file
  3. Copy the link provided
  4. Open Power BI Desktop
  5. Click Get Data –> Web, then enter the URL
  6. Select the table and click Load

Modeling the Data

For a general guide to data modelling with Power BI, see here.

By creating a relationship between multiple fields and their unique audit ID, you can dynamically filter data: see here for finished examples.

In order to create this relationship, we first need to create a new calculated column that contains the unique audit IDs.

  1. To create a calculated column, select the Data view in Power BI Desktop from the left side of the report canvas.
  2. From the Modeling tab, select New Table. This will enable the formula bar where you can enter calculations using DAX (Data Analysis Expressions) language. DAX is a powerful formula language, also found in Excel, that lets you build robust calculations. As you type a formula, Power BI Desktop displays matching formulas or data elements to assist and accelerate the creation of your formula.The Power BI formula bar will suggest specific DAX functions and related data columns as you enter your expression.
  3. Copy and Paste the following expression into the formula bar:
    auditIdTable = FILTER(DISTINCT(TEMPLATE_ID[Audit ID]))
    replacing TEMPLATE_ID with the template ID associated with the CSV file you’ve imported.

Next, you will create another table for the field you want to associate with a specific Audit ID

  1. Follow step 1. above to create a new table.
  2.  Copy and Paste the following expression into the formula bar:
    TABLE_NAME = SELECTCOLUMNS(FILTER(TEMPLATE_ID, FIND("ITEM_ID", TEMPLATE_ID[Item ID], 1, 0) > 0), "Audit ID", [Audit ID], "COLUMN NAME", [COLUMN])
    Replace TABLE_NAME with the name you would like to name this table.
    Replace TEMPLATE_ID with the template ID associated with the CSV file you’ve imported.
    Replace ITEM_ID with the Item ID of the field you are want to be mapped. This value can be found in the Item ID column of the CSV export.

Manage Data Relationships

Lastly, you will set the relationship between the field and its unique audit ID. Power BI allows you to visually set the relationship between tables or elements. To see a diagrammatic view of your data, use the Relationship view, found on the far left side of the screen next to the Report canvas.

From the Relationships view, you can see a block that represents each table and its columns, and lines between them to represent relationships.

Simply click and drag the audit ID in theauditIdTable and drop it on the Audit ID in the table created for the field you mapped.

You can follow this same process for any number of fields, which will allow the fields to interact dynamically when you create visualisation filters.

Here is an example of a working Relationship view, using SafetyCulture audit data:

You can see the raw data table on the left, and the Modeled data on the right, all connecting back to the unique audit ID table.