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
alvin199
Helper III
Helper III

Arrange Multiple Column Values into Rows in Fixed Order

Hi,, I have a survey question response that the users able to select multiple selection up to 5. Each selection response is recorded into individual column. 

 

In this table below, each selection consist of 3 items, i.e Country, Percentage & Amount. It is fixed for each selection must fill up 3 items.

alvin199_0-1654763568420.png

 

My question is how can I arrange all 5 selection response into only 3 columns called Country, Percentage & Amount. I am aware of repetitve data value on early column before them (ex. Name & Gender of the respondence)  so when I am selecting any column (before the multiple selection) I need to use distinct (means no duplication).

1 ACCEPTED SOLUTION

Hi, @alvin199 

As a workaround, you can create 5 tables to extract the data of these 5 areas. After filtering out the null values of the last four tables, you need to append these new tables.

veasonfmsft_0-1655287734805.png

 

Best Regards,
Community Support Team _ Eason

View solution in original post

3 REPLIES 3
selimovd
Super User
Super User

Hey @alvin199 ,

 

you can archive that by unpivoting the columns in Power Query.

Check the documentation for more information:

https://docs.microsoft.com/power-query/unpivot-column

 

If you post the content of the table I can also give you an example how that would look like.

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic

Hi @selimovd 

 

The unpivot seems does not show the expected result. I wish to have it like in this format 

alvin199_0-1654767152745.png

 

 

Here is the sample data that I have used.

https://docs.google.com/spreadsheets/d/1MUXZ68khj88B_psIttIs1YPf7hQ6nzdf/edit?usp=sharing&ouid=11028...

Hi, @alvin199 

As a workaround, you can create 5 tables to extract the data of these 5 areas. After filtering out the null values of the last four tables, you need to append these new tables.

veasonfmsft_0-1655287734805.png

 

Best Regards,
Community Support Team _ Eason

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.