Deep dive: Questionnaire visualization in Power BI

Resco questionnaires are digital forms, usually running in Resco mobile apps, that allow you to collect data in the field. Resco Inspections come with a powerful reporting function for creating PDF or other documents from a completed questionnaire directly in the mobile app. However, in some scenarios, you might want to visualize the collected data in Power BI instead.

In the following steps, we describe the process of displaying answers collected on the questionnaire in the Power BI report. We tried to keep the steps as general as possible so that you can apply the steps in your use case.

Report data: questionnaire

It's essential to have a precise understanding of what you want to visualize before you start working on the report. In our example, we visualize responses gathered from a questionnaire for a mobile elevator work platform. This questionnaire has question group "01 Inspection start". This question group contains two questions we plan to visualize in the report.

Questions we want to visualize

• The first question is, "Can the device be inspected?". The answers to this question are in the form of Yes/No. On the report, we want to count the number of "Yes" and "No" answers.
• The second question, "Reason", is visible only if the previous question, "Can the device be inspected?" was answered "No". We will display the number of different reasons on the pie chart.

Entities we use

The following entities are used in the report: Account, Asset, Workorder, and Questionnaire.

The logic behind our use case is following. Account own Assets. You can schedule Workorders for Assets. The objective of the Workorder is to perform an inspection, which results in a Questionnaire.

Answers from the questionnaire are stored in the JSON format. The string of this JSON is then stored in the resco_serializedanswer field within the Questionnaire entity. We can use Power BI to transform (parse) the JSON and visualize the data collected in the questionnaires.

Report design

This is the report we want to create.

It contains the following blocks:

Filters (Slicers)

• Account filter
• Workorder/Asset filter
• Inspector filter
• Timeline filter

Visualizations

• 2 cards
• Stacked bar chart
• Donut chart

Creating the report

This is a high-level overview of the necessary steps.

1. Feed data into the Power BI Desktop
2. Clean up the data, keeping only the parts necessary for the report
3. Transform (parse) JSON
4. Verify the relationship model
5. Build the report

Get data into Power BI Desktop

1. Install Power BI Desktop to your computer. (What is Power BI Desktop?)
2. Import the data from the backend. Click Get Data in the top bar.
   
3. Connect to the selected backend:
   • If you are working with Dataverse, enter "Dataverse".
   • If you are working with Salesforce, enter "Salesforce objects".
4. Select the entities you wish to visualize in the Navigator pane.
5. Click Transform Data.
   
   

   Additional information: All selected entities are loaded by clicking Transform Data, and Power Query Editor opens. Alternatively, you can click Load to load the data and then manually enter the Power Query editor.

6. On the Connection settings window, select Import, then click OK.
   

   Additional information: Import mode downloads data from selected entities from the database to your computer. DirectQuery creates a live connection to the database. While DirectQuery solves the problem with the data size, it negatively affects the performance and does not allow data transformation. Given our need for data transformation to parse the JSON, we must utilize Import mode.
   If you work with unusually big data and want to keep the dataset as small as possible, use import mode only for the Questionnaire entity and DirectQuery for the rest. You can do this by going through the Get data process twice and, each time, choosing a different connection mode.

Clean up the data

Use the Power Query editor to perform data cleaning. Data cleaning depends on your scenario. Generally, remove the columns and rows that you don't need.

1. Filter the Questionnaire entity by completion status and serialized answer field. We want only the questionnaires that are completed and contain JSON strings.
2. Once we have performed data cleaning, it's time to create a new query where we parse the JSON. Start by creating a reference from the Questionnaire query. In the Query pane, right-click "resco_questionnaire" and select Reference.
   
3. In the new query, filter out the rest of the questionnaire templates so only the template you wish to visualize is present.
4. Isolate the resco_serializedanswers column containing the JSON. Right-click the column header and select Remove Other Columns.
   

Transform JSON

Now that the resco_serializedanswers column is isolated, it's time to parse the JSON.

1. Right-click the header of the resco_serializedanswers column and select Transform > JSON.
   
2. The JSON records have now been transformed into nested records. To see the record's contents and later expand it, click the expand button (right corner of the column header).
   

   In our case, we have five nested columns:

   • 01-inspection-start (question group)
   • 02-characteristics (question group)
   • @q (questionnaire info)
   • @root (root questions)
   • @ver (questionnaire versioning)
     
     

   Question group 01-inspection-start contains the questions we want to visualize. @q contains questionnaireid, which is needed to create a relationship with the other entities later. (See JSON storage for questionnaires if you need to understand the JSON format better.)

3. Select the columns you need and click OK.
4. Once those two columns are expanded, repeat the step until you get the questionnaireid from @q and the values/value labels from their respective questions and question groups.
5. Optionally, rename the new columns to make the work with them more intuitive. This is the final version of our questionnaire template query.
   
6. Click Close & Apply to close the Power Query Editor.

   Additional information: As mentioned in the beginning, we should have a clear picture of what questions we want to visualize and where exactly they are located. To gain a better understanding of where the specific question or information is, it's recommended to look at the formatted JSON beforehand.
   
   In this picture, you can see the 01-inspection-start question group. We are looking for the 01-01-can-the-device-be-inspected.., question value label. By looking at the JSON, you can see that the question value label is nested in three levels.

Tweak the relationship model

Next, define the relationship model. Click Model View in the left pane.

Power BI automatically determines the relationships between the tables based on the lookup columns they contain. However, this automatic assessment is not always ideal. In our model, we have four imported tables (account, asset, workorder, questionnaire) and one table containing a questionnaire (mobile_elevating_work_platform), where we transformed JSON and expanded the answers. In our sample scenario, we decided to go with a Workorder-centered model.

Relationships in our model:

• Account (accountid) 1:N Workorder (msdyn_serviceaccount)
• Asset (assetid) 1:N Workorder (msdyn_customerasset)
• Questionnaire (resco_workorder) N:1 Workorder (WO number)

To add the questionnaire to the model, create a 1:1 relationship with the Questionnaire table: mobile_elevator_work_platform (ID) 1:1 Questionnaire (questionnaireid).

Additional information: You might ask, "Why did we use a Workorder-centered model?" The reason is that this way, we can visualize multiple questionnaire templates. We can repeat the steps we did for the mobile_elevating_work_platform table in Power Query Editor and create another table containing a different questionnaire template. Then, add it to the model as in the previous step. The model should then look like this:

Now, we have access to the answers from three questionnaires at once. When visualizing multiple questionnaires, the ideal solution is to create a Dashboard on page 1 of the report and then add redirection to a page dedicated to a specific questionnaire.

Build the report

Moving ahead, our focus will shift toward a report specific to our use case. At this point, you should have the questionnaire answers expanded into the columns and a relationship model established. You can draw inspiration from this section as you proceed.

1. Switch to Report view and add four slicers (filters).
   
   • Account slicer: we add Account Name field
   • Asset and Workorder slicer: we add Asset Name field and Workorder Number field
   • Inspector slicer: we add Created By field (Questionnaire entity)
   • Created on slicer: we add Created On field (Questionnaire entity)
     

   Additional information: To make the slicer work properly, we need to do two things: create a measure and add it to the slicer as a visual filter. Right now, the slicers show every record from their respective table. For instance, the Account slicer shows all accounts, not just the ones related to our questionnaire. The problem is, that if we choose an account that hasn't completed the questionnaire, the report will be empty.
   To fix this, create a measure that counts the number of questionnaire IDs. Then we'll add this measure to the slicer. The slicer will only show records where the count of questionnaires is more than zero. This means there's at least one completed questionnaire.
   
   DEX formula: Measure = COUNT(mobile_elevating_work_platform[ID])
   Lastly, add the measure as a filter on this visual to your slicers.
   

   Once the slicers are set, it is time to add Cards.

2. Go to the Visualizations panel and click Card. Add the ID field to the Card from the mobile_elevating_work (questionnaire) table. Currently, the card displays the ID of the first questionnaire in our table. We want the count of the IDs. In the visualization pane, under the Fields section, click the dropdown next to the selected field a choose Count.
   
3. Add another card right below the previous one. This time, we count the number of successful inspections (where the answer to the "Can the device be inspected?" question was Yes. Repeat the steps from the previous card. Once we have the same card, it's time to add a filter to the visual. Select the card, and in the Filters panel, add the CanBeInspected field to the filters on this visual. Select Yes answer.
   
4. Add a Stacked bar chart to the report. As mentioned before, we have a "Can the device be inspected?" question, with Yes/No answers. Add the questionnaire ID field on the X-axis to count the number of answers. To differentiate between the Yes/No answers, add CanBeInspected (column containing the answer) on the Y axis.
   
   

   The final visualization we include in the report is a Donut chart. In this chart, our goal is to show the different reasons why the inspection couldn't be completed.

5. Like the stacked bar chart, we'll use the ID as a value to represent all the questionnaires. Next, we'll add Reason as a chart Legend.
   Additionally, we have to add a filter to this visual. If the answer to the previous question was Yes, the Reason was filled as null. We don’t want to display null answers. Add the CanBeInspected (question) field to the filter and select No. This will show only those Reasons where the answer to "Can the device be inspected?" was No.
   
   

Data usage overview

If you are concerned with the Power BI data size, use DAX Studio. Connect to the opened report, go to the Advanced tab, and click View Metrics. Here you can see how much data is each table and column using.

See also

Alternatively, check out the following video that walks you through a very similar scenario.