Guidance on categorising data from an Microsoft Forms survey

[PowerMyBI]

Hi BI forums, I've performed a detailed survey using Microsoft Forms which outputs all of the results into a spreadsheet. The survey had multiple sections and unfortunately sections do not export to the table. I'm looking for a way to sort the many columns of the table into the sections from the original survey as I'll need to be able to visualise the data by section. I also need to be able to sort the multi-choice responses. I'm familiar with defining a custom sort order using an additional table but I'm unsure how to appply the sort order to multiple columns

Example data;

Name	Word Experience	Excel Experience	Photoshop Expereince	Indesign Experience
Sarah	Basic level	Advanced	Intermediate	Advanced
John	Intermediate	Basic level	No experience	No experience
Mike	Advanced	No experience	Intermediate	Advanced
Emily	Advanced	Intermediate	Basic level	Basic level

I would like to know the best method to categorise the columns above for example Word Experience and Excel Experience would both fall under the 'Office' section while Photoshop and Indesign would be 'Adobe'.

Additionally I'll need to be able to apply sorting to multiple columns. For example;

Response	Sorting
No experience	0
Basic level	1
Intermediate	2
Advanced	3

Thanks in advance for any tips and pointers. Much appreciated.

[v-stephen-msft · Accepted solution]

Hi @PowerMyBI ,

Here's the solution.

1.Select the Name column, click 'Unpivot Other Columns'.

2.Create a calculated column for sorting. 

Sorting = 
RANKX (
    'Table',
    CALCULATE (
        COUNT ( 'Table'[Value] ),
        FILTER ( 'Table', [Value] = EARLIER ( 'Table'[Value] ) )
    ),
    ,
    ASC,
    DENSE
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@PowerMyBI , My advice would be to unpivot this table and use

https://radacad.com/pivot-and-unpivot-with-power-bi

[PowerMyBI]

Thank you @amitchandak that is indeed the best approach. Do you have any advice for how I might be able to apply a sorting to multiple columns in the most efficient way?