Search code examples
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

    • Your first step should be splitting the data from the different questions into separate tables, then "Unpivot Other Columns" but Response ID. You can later relate the tables in the report via this Response ID.

    • From here creating your column charts should be a no-brainer.

    • Starting with a list of question identifiers ("Q1", "Q2", ...) you can automate the splitting via a Custom Function

    • I doubt that with Qualtrics Excel imports you'll come anywhere close to Power BI's data limits. However, I am surprised that they are providing such an awkward interface.