Arrange Multiple Column Values into Rows in Fixed Order

[alvin199]

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.

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).

[selimovd]

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

[alvin199]

Hi @selimovd 

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

Here is the sample data that I have used.

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

[v-easonf-msft · 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.

Best Regards,
Community Support Team _ Eason