It is possible to import your iAuditor inspection data into the Power BI application, where you can create useful visualizations of your data (see here).

Loading Data into Power BI Desktop

  1. Follow instructions here to export inspection (audit) data as CSV using the 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.
  6. Note you can also import the CSV file directly to Power BI online:

Model the data

For a general guide to data modeling 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.
  3. 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.
  4. Copy and Paste the following expression into the formula bar:
    auditIdTable = DISTINCT(TEMPLATE_ID[Audit ID])
    replacing TEMPLATE_ID with the template ID associated with the CSV file you’ve imported.
  5. Next, you will create another table for the field you want to associate with a specific Audit ID
  6. Follow step 1. above to create a new table.
  7. Copy and Paste the following expression into the formula bar:
    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.

Note that the [Response] near the end of the query can refer to any column from the initial CSV table imported.

Manage Data Relationships

  1. Set the relationship between the field and its unique audit ID. Power BI allows you to visually set the relationship between tables or elements.
  2. 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.
  3. From the Relationships view, you can see a block that represents each table and its columns, and lines between them to represent relationships.
  4. Click and drag the audit ID in theauditIdTable and drop it on the Audit ID in the table created for the field you mapped.
  5. Follow this same process for any number of fields, which will allow the fields to interact dynamically when you create visualization filters.

Here is an example of a working Relationship view, using iAuditor inspection data. ou 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.

Was this article helpful?

We love your feedback. Please tell us what you think.

Yes No
Care to share a bit more so we can continue to make improvements for you? Care to share a bit more so we can make this article even better for you? Thanks for your feedback!