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.
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?
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.
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.
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!
Share some data and show the expected result.
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.
In the Query Editor, right click on the Reason column and in the split by, select comman. Change the split by setting to rows.
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.
We are excited to announce the Power BI Super Users!
Overview of Power BI 2020 release wave 2!
Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.