Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chniva
Regular Visitor

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?    

1 ACCEPTED SOLUTION
Saurabh8437
Frequent Visitor

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.

View solution in original post

6 REPLIES 6
chniva
Regular Visitor

Are you saying a different table for each nationality or one table and then unpivot?

chniva
Regular Visitor

That makes sense.  I was hoping to not have to do so much 'cleaning', but that is the best option.  Thank you!

Saurabh8437
Frequent Visitor

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
Super User
Super User

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-column

Thanks ,
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 C





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Hi 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?

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.