converting multiple columns to rows

[haniizz]

hello guys , I have this table as a dataset for a survey collected from 11 respondents

respondent	best color / red	best color / orange	best color / yellow	best color / green
1	0	0	1	0
2	1	0	0	0
3	1	0	0	0
4	1	0	0	0
5	0	1	0	0
6	1	0	1	1
7	0	1	1	0
8	0	0	0	1
9	0	0	1	0
10	0	1	0	0
11	0	1	0	0

how can load this data that will allow me to analyse it like the table below

best color	responses
red	4
orange	4
yellow	4
green	2

and allow me to visualise it like this chart

I hope I simplified my  question

[djurecicK2 · Accepted solution]

Hi @haniizz ,

After loading your data, click Transform Data to open the Power Query Editor.

Then CTRL+Click to select all of the color columns and click Unpivot Columns.

Click Close & Apply to close the Power Query Editor.

The next step is not strictly necessary, but click "New Measure" and create a measure like below (adjust for your table and column names)

SumValue = SUM(Colors[Value])

Then add a clustered column chart like below

The chart will look something like this:

Please accept as solution if you think this has answered the question.

[haniizz]

thank you dj for you prompt response, that's helpful