Forum Discussion
Survey data with multiple columns for nationality
The data has 9 columns with a different nationality in each column. For example, column 1 is white, column 2 is Hispanic, column 3 is African American, etc. Unfortunately, the survey allowed the person to select more than one nationality. So I could have white and Hispanic which makes merging the columns a problem.
What can I do so the user can select one of the nationalities and still show the survey data accordingly?
Hi chniva,
If there are multiple columns to unpivot, what I do is to duplicate the table and split the columns among the tables and then load all and create relationship and use them in the power bi dashboard.
Like two tables with two different columns or information and keep the survey ID or something common to create relationship and later show them together in the dashboard.
6 Replies
Hi chniva,
If there are multiple columns to unpivot, what I do is to duplicate the table and split the columns among the tables and then load all and create relationship and use them in the power bi dashboard.
Like two tables with two different columns or information and keep the survey ID or something common to create relationship and later show them together in the dashboard.
- pratyashasamal
Memorable Member
Hi chniva ,
You can use unpivot column option to resolve this issue .
You can combine all the nationality column into attribute and value column .
You can follow this link for refernce : https://learn.microsoft.com/en-us/power-query/unpivot-columnThanks ,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos CHi Pratyasha. Thank you so much for the reply. That is a great idea. What if I need to unpivot additional columns? The rest of the survey is structured the same way. The questions are in columns and not rows. In those cases, I used 'unpivot other' to restructure the data, but those surveys did not have multiple nationality columns. What do you recommend to restructure the questions and nationality?
- pratyashasamal
Hi chniva ,
This is going to be a dynamic process . You unpivot all column expect the name column after separtion each nationalities.
Thanks ,
Pratyasha
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
That makes sense. I was hoping to not have to do so much 'cleaning', but that is the best option. Thank you!
Are you saying a different table for each nationality or one table and then unpivot?
