cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

how to split column with multiple choices on a survey

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

9 REPLIES 9
Helper I
Helper I

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.

Super User III
Super User III

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Community Support
Community Support

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.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Super User II
Super User II

@Dee - 

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

 

PowerBI - Split Column (Comma's) into Rows.jpg

 



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

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!

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.  

sample_data.png

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors