Hello,
I have a problem to properly format the data from an Excel document to use it within Power BI.
The Excel looks like this (simplified):
ID | status | language | ... | category1, question1 | category1, question2 | category1, question 3 | category2, question1 | category2, question2 | ...
The "question"s have results that convert into numbers.
In PowerBi I want to show different statistics how the categories perform within the different questions, e.g. a bar chart for "question1" where the bars represent the values of category1 to category50.
I'm quite sure I have to unpivot data somehow, but I don't really know how to approach this particular use case.
I guess the best result would be a table containing data like this:
ID | .. | category | question1 | question2 | question3 | ...
(category then would have category1, category2 or category3 as content. This of course would contain dublicated IDs and other fields, but that I could filter out by grouping by ID later).
Thanks so much,
Tom
