How to collect and calculate number of occurrences from multiple columns

[Anonymous]

Hi all, 

I would like to collect various descritive values from multiple columns containing some similar values in various columns. So the content is mostly standard but based on Forms questionnaire people may input their own preferences as well.

Below an example of the source data (describing fruits people prefer having):

The desired output would be like this. 

Thereafter I could select one of the fruits and if I have people listed in another table the ones that like that particular fruit would be highlighted.

Any tips?

[v-yuta-msft · Accepted solution]

@Anonymous ,

To achieve this, click query editor-> click on Fruit1~Fruit5-> click Transform-> Unpivot columns.

 

After filter the blank values in Value column, click Group By based on Value column.

 

The result will be like below:

 

Community Support Team _ Jimmy Tao

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

[Anonymous]

Awesome @v-yuta-msft, thanks a million. I was sure there was a way, just couldn't figure it out quickly.

Thanks again.