Power BI can be useful for displaying and storing survey results for your organization. In this example we build some data for the results of a fictitious Airline survey, using questions taken from a real online airport survey.
The data was built in Excel with a sheet for questions and a sheet for answers. You can download the Excel file here.
It’s easy to publish the report as a PDF, so you can share it with users who don’t have access to Power BI. Here is the airport survey report as PDF
The questions are a simple list with a Question ID and section.
The answers on the second sheet are formatted so that each row represents all the answers for each respondent.
Steps for Building the Airline Survey Report.
Step 1
The Data is imported to our Power BI Desktop survey using Power Query.
The only change to the question table is a simple column rename.
The answer table is transformed so that each line of the table contains the respondent ID, the question, and the Value (the answer). This prepares the data so it can be joined to the question table and easily visualized.
The applied steps are as follows:
As you can see the first step after the import is simply to promote the headers, so the column names are the actual column names.
The second step is just to change the column types to the correct types.
The key third step is to unpivot the data, so the table is transformed as below a separate row for each respondent’s answer. This makes the report easy to build.
The report only requires one measure, which is just a unique count of the respondent ID
Answer Count = DISTINCTCOUNT(Answers[Respondent])
Basic Graphs can be added.
And Likert Scale style graphs using a 100% Stacked bar graph visual.
Its a simple, but useful report.
Leave a Reply