Forum Discussion
converting multiple columns to rows
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
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 belowThe chart will look something like this:Please accept as solution if you think this has answered the question.
2 Replies
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 belowThe chart will look something like this:Please accept as solution if you think this has answered the question.thank you dj for you prompt response, that's helpful
