Although you can configure your Helm CONNECT reports to display the actual values recorded in a checklist, audit, or form, they'll appear in separate rows which may not be the format you want. This article will show you how to use Microsoft Excel to transform your data so the values appear on one row for each individual audit, form, or checklist.
You can use this procedure to transform exported reports for the following Helm CONNECT data sources:
- Inspection Checklists
- Inventory Checklists
- Maintenance Checklists
Note We tested the procedure in this article in Microsoft Excel for Office 365 and Excel 2019. We can't guarantee it works with older versions.
Generate and export the required report
The first thing you'll do is create a report that includes your key fields, and then export it from Helm CONNECT.
To generate and export the required report
- In Reports > Reports, click New Report or select an existing report.
- If you create a new report, in the Name field, enter a name.
- Set the required Date Range for the report.
- Use the filters to narrow the report to display only the information you need.
- From the Data Source list, select the required report type.
- From the list on the left, select the fields you want to include in the report. See the "Tips for making your report" section below.
- Click Save Changes.
- Copy the CSV connection string value or click Export to CSV to export the report information.
Tips for making your report
For clarity, we recommend that you limit the number of columns in your report and arrange the columns in the following order, from left to right:
- Asset Name
- Date (whichever one you want)
- External Number (if used)
- Any other items from the main section of the list that makes sense for the report
- Item Values fields
We also suggest that, in the Item Values group, you select only the items indicated below. If you select other items, the exported report may show more "null" values and require additional filtering.
Transform the exported report
Once you've generated and exported your report, you'll use the Power Query tool in Microsoft Excel to transform the data in the exported report.
To transform the exported report
- In Microsoft Excel, click the Data menu.
- Select one of the following options:
- If you exported the report from Helm CONNECT to a CSV file, click From Text/CSV, locate the exported file, then click Import. When the preview window appears, click Transform Data.
- If you're using the CSV connection string, click From Web. In the window that appears, paste the CSV connection string value, then click OK.
- In the Power Query Editor window, click the Description header (the label for this header might be something different).
- On the Transform toolbar, click Pivot Column.
- In the Pivot Column window do the following:
- In the Value Column list, select the Value field (for Inventory Checklists this will be the Count field).
- Expand Advanced options. From the Aggregate Value Function list, select Don't Aggregate.
- Click OK.
Each row now displays the information for one completed item (checklist, audit, or form) for one asset.
- On the Home toolbar, click Close and Load to display the transformed file in Excel.
- (Optional) Use Excel's filters to further refine the display of information.
- Save the transformed file.
Note If the exported report has a number of blank fields, or a large number of component-specific fields, you could end up with more than one row per audit, form, or checklist instance, and there could be a large number of cells that contain “null”. This is okay and expected, but not so easy to read. This is the main reason we recommend selecting only specific fields under the Item Values group when you build your reports (see the "Tips for making your report" section above). You can use the filter tools in Excel to display and sort the information you want to see and hide the data you don't need. However, how to use the Excel filters is beyond the scope of this article.