Once you have retrieved the data from the survey you are interested in, you can use the CodePlan table from the template file to replace the numerical values with the choice labels in the given questions.
In the following instruction you will find a step-by-step process on how to create a mapping for your Survalyzer survey in PowerBI.
Prepare a code plan for mapping
Select the ‘CodePlan’ table and filter the ‘Column1.questionCode’ column, removing empty question codes (these with a value ‘null’).
Do the same for the column ‘Column1.values.id’.
Change the value type in ‘the ‘Column1.values.id’ from mixed to ‘Whole number’ (by left-clicking on the ‘ABC/123’ icon in the top left corner of the column).
Apply the necessary transformations to the GetInterviewData table
Select the ‘GetInterviewData’ table.
Hold down the CTRL key and select all the questions for which you would like to apply the mapping. Then right-click the selected question columns and choose ‘Replace values’. In the ‘Value to find’ field, type null, and in the ‘Replace with’ field, type 0.
Right-click the selected question columns again and choose ‘Unpivot Columns’.
Select the ‘GetInterviewData’ table (if it is not selected) and go to Home > Merge Queries.
You should see an open dialog window with the ‘GetInterviewData’ table selected at the top. Then select ‘CodePlan’ at the bottom.
Match the ‘Attribute’ column in the GetInterviewData table to ‘Column1.questionCode’ in the CodePlan table.
Do the same for the ‘Value’ column in the GetInterviewData table with ‘Column1.values.id’ in the CodePlan table (within the same window). To do this, hold down the CTRL key to select the two pairs of columns. If you do it correctly, you will see index 1 for the first matched pair of columns and index 2 for the second pair. The indexes are displayed next to the names of the selected columns.
Use the ‘Left Outer Join’ option and click OK.
If everything is correct, you should see a message that the selection matches more than 0 rows.
After merging, expand the CodePlan table in GetInterviewData and select only the ‘Column1.values.value’ column from CodePlan to display the question labels.
Once you have done this, the relevant labels should appear in the rows. Then rename this new column to ‘Label’.
Remove the original ‘Value’ column.
In the dialog window, select ‘Label’ as ‘Values column’ and choose ‘Don’t aggregate’ in the advanced options.
As a final result, you should see several new columns in the GetInterviewData table. The number of columns should be equal to the number of questions for which you wanted to prepare the mapping.
