I have been using Power BI to evaluate survey data for several months now, and it has been working well. However, I think I'm going the long way around something, so I'm wondering if any of you smart people out there can give me a short cut.
I need to get a summary of text responses (count, percent of GT) as well as the mean scores for each response.
When dealing with Likert-type scales in survey data (e.g., 1=never, 2=occasionally, 3=often, 4=always), I have been importing two Excel tables, one with the value labels (e.g., never, often) and then an identical table with the values (e.g., 1, 2, 3, 4). It takes some time to recode all those text labels into values.
There has to be an easier way to do this. I know I can create a table that references the text lables with the values, but that's as far as I've gotten.
So this isn't a perfect approach, but here's what I did...
First, in Power Query I copied the query, Chose Columns to include only the ID and the Likert Items, and then unpivoted the Likert scale so that I had a zillion rows of Attribute/Value pairs. Then, I added a Conditional Column (cheating using Column from Examples) to give a numeric value to each of my values. In my case, Strongly Disagree = -2, Disagree = 1, Neither Agree nor Disagree = 0, Agree = 1, Strongly Agree = 2.
Loading this into the data model, Power BI auto-detected the ID as a key field and built the relationship between my main table and my "Likert Question" table.
To visualize, I just used a horizontal barchart using the Question for the axis and the "Response_Numeric" for the values (using the "Average" summarization), plus a custom X axis label (with a bunch of underscores, since PBI ignored multiple spaces). I also force-set the X axis so that the Min was -2 and the Max was 2 so that the scale stayed true to the feedback.
It doesn't give the full nuance that the native MS Forms tool does and it's not super-pretty, but it does show the gist of the data as you apply filters based on other demographics. Here's what it looked like (note that this was just a subset of my data, but it highlights how it looks):