Structure data from questionnaire

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