Deep dive: Aggregate questionnaires via Power BI template

Warning

Work in progress! We are in the process of updating the information on this page. Subject to change.

In this guide, we will demonstrate a prebuilt Power BI template for deserializing questionnaire answers. Questiannaire answers are inherently stored in the resco_serializedanswers columns in JSON format. To use this data in external reporting tools, we need to deserialize it back into a structured format.

In [link to deep dive], we deserialized JSON manually using Power Query. In this guide, we will use a Power BI template. Power BI templates are pre-built templates that usually include parameterized imports, Power Query, and visualizations. Templates are used as an industry standard for report distribution.

Download Power BI templates

For your convenience, we provide two templates depending on your storage entity:

• questionnaires
• questionnaireanswers

About the template

The template consists mostly of queries, functions, and parameters. Visuals are not included, as the questionnaire data is use-case-specific.

Parameters

You have to define the parameters as soon as the template opens.
[Power BI template parameters inicialization.]

Mandatory parameters

• OrgUrl - clean Environment URL to which you want to connect (no https:// or tailings .../).

If only the OrgUrl param is defined, all questionnaires are queried and deserialised. You can then define a query for each template separately. TemplateA query is an example you can duplicate or edit.

Optional params

• TemplateID - If you wish to deserialize one specific questionnaire template, paste templateid into this parameter.
• Questions -If you wish to deserialize a specific questions in selected template, you can use this parameter, e.g. model, serialnumber, ... .

Queries

• Questionnaires query contains all questionnaire data as is from the dataverse.
• Answers query contains deserialized and processed answers in the form of records. This query is pre-pivot. That means a final pivot query is necessary to structure the data in the columns: question name, and the rows: question answer format.
• TemplateA is a sample query containing the final pivot step. This query can be duplicated or edited in the advanced editor.

To pivot the data in TemplateA or its clone, you have to specify templateid in Power Query.

1. In the top panel click Transform data.
   
2. Select TemplateA query.
3. In the Query Settings, under APPLIED STEPS, select the FilterTemplate step.
   
4. In the Power Query editor, fill in the empty bracket with templateid.