powerbiunpivotqualtrics

How to plot clustered bar chart from raw Qualtrics excel data?

My raw Qualtrics data looks something like this. Basically just 2 questions. Q1-where did you learn your tech skills from? and Q2-Do you agree with the following statement?

I want to plot clustered bar chart in Power BI that looks something like the link below. Basically, for Q1-where did you learn your tech skills from? Each cluster is a channel of learning, and within each cluster there is a standard response from not at all, to a small extent, to a moderate extent, to a great extent, entirely

I figured out I cannot plot straight from the raw Qualtrics data. However, if I unpivot just the columns for Q1, I can get the above clustered bar chart.

But here comes the problem. I have other questions with the same raw Qualtrics format. So I tried to unpivot columns for Q1 FIRST, and THEN unpivot the columns for Q2, and got the following, which does not make sense because Q1 has 4 sub-questions while Q2 has 5 sub-questions. This is like a m:m joins (if I make sense?)

So I thought maybe I could unpivot all the columns except for the Response ID column and I got this

Doing the above has several issues;

  1. the number of rows gets large exponentially and imagine if I have many more questions and many more respondents, the data format just gets too large;
  2. when I want to plot the clustered bar chart, I have no way to restrict the rows just to plot for Q1, or rows just to plot for Q2 etc.

I tried googling and was surprised there isn't a similar question before? given how Qualtrics is very well used for survey data.

Appreciate all your help in advance!

Solution