Forum Discussion
Post ProdigySort 78 columns in a large questionary
I have this large table with respondents in the first column and then each question per column. It consist of 78 columns with more or less the same type of possible answers like:
Question: Did You like the seminar?
Answer possibilities:
1. Very much
2. Very
3. Indifferent
4. Little
5. Not at all
Datas in my table:
First of all I need to cross questions (question a with question c etc.), and pivoting will make that impossibleI, therefor I need to sort each column in a specific order. I know I can add a sort column, but then I need to add 78 calculated columns. In power query I build the dataset from several API connections and in the power query part I can't sort. I also am aware that I can build a question (dimension) for each question, but that is also extremly cumbersom. For now I have added the sort value to the answer, but that looks stupid imho.
How to sort so many columns?
Hi Bokazoit ,
Firstly unpivot columns and normalize Your Data, and then create a separate sort key table like
Answer Text Sort Order Very much 1 Very 2 Indifferent 3 Little 4 Not at all 5 In power query merge this sort key into your unpivoted table based on answer, so that you'll now have a numeric sort order column applied to every row, regardless of question. And now in power BI model view set answer text to be sorted by sort order. After this you can still do Cross Questions by using DAX measure.
If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.
Regards,
Chaithra.
7 Replies
- Cookistador
Super User
Hi Bokazoit
For me, the best and most scalable solution here is to unpivot your data in Power Query and then leverage a single sort column for your answer values.
BokazoitI need to cross questions (question a with question c etc.), and pivoting will make that impossible
v-echaithraCommunity Support
Hi Bokazoit ,
Firstly unpivot columns and normalize Your Data, and then create a separate sort key table like
Answer Text Sort Order Very much 1 Very 2 Indifferent 3 Little 4 Not at all 5 In power query merge this sort key into your unpivoted table based on answer, so that you'll now have a numeric sort order column applied to every row, regardless of question. And now in power BI model view set answer text to be sorted by sort order. After this you can still do Cross Questions by using DAX measure.
If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.
Regards,
Chaithra.
