how to split column with multiple choices on a survey

[Dee]

Hi all,

I'm running an analysis on a survey collected.

One of the questions, however, allowed multiple answers, whereas most people picked all hence the data looks something like this.

Response

A|B|C|D

A

B|C|D

B|C

The data has other columns like name, age, location, occupation, etc.

I'd love to split that column so as to use the keyed in responses as column headers, and use 0 and 1 to confirm if someone had chosen the response.

Something like this.

A B C D

1 1 1 1

1 0 0 0

0 1 1 1

0 1 1 0

How do  I achieve this by still keeping the other column intact?

TIA

[Anonymous]

I did it using stata to reorganize the database.

Usually the database comes a variable for each response, so you have to reorganize the base so that all the answers remain in one column, therefore you will increase the number of observations but taking into account the unique identifier or code that there is for each individual/observation.

I used the command:

reshape long nombredelavariablequequieras, id(Identificadordelaobservacion) j(VariableA VariableB VariableC)

any doubt is the help of stata(https://www.stata.com/manuals13/dreshape.pdf)

Then I export it to excel and charge it to PowerBi as a new base, it is related to the original base by the identifier or key.

I hope it works for you.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[v-diye-msft]

Hi @Dee 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[nandukrishnavs]

@Dee - 

In the Query editor, You can split your column into rows.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[angie01]

Can I split two colums with multiple answers in them, within a table that has lots of other columns without messing anything up? eg row-wise? Thank you!

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[angie01]

Here is a snapshot of the data. Expected result is to calcualate each individual Reason, as separated by comma,  so that i can report the data in a pie chart, for example.  

[Ashish_Mathur]

Hi,

In the Query Editor, right click on the Reason column and in the split by, select comman.  Change the split by setting to rows.

[Dee]

Hi  @nandukrishnavs 

Thanks, I had tried this but the issue with that is that the responses are in no particular order hence hard to rearrange for the desired output.