Unpivot Repeating Attribute Value Columns

[Matthias]

Hi

I am working on a report in an IT context. I have a table 'Projects', the work of each project can be distributed according to several domains. Users enter this information in Domain - Distribution Percentage Fields. They have the option to select 5 domains and assign distribution percentages.

This leads to the following dataset.

ID	Domain1	Domain1 Percentage	Domain2	Domain2 Percentage	Domain3	Domain3 Percentage	Domain4	Domain4 Percentage	Domain5	Domain5 Percentage
1	SAP	20	CRM	20	MES	20	Sharepoint	20	Server	20
2	MES	80	CRM	20
3	SAP	100

How can I transform my dataset to achieve the underlying table?

ID	Domain	Percentage
1	SAP	20
1	CRM	20
1	MES	20
1	Sharepoint	20
1	Server	20
2	MES	80
2	CRM	20
3	SAP	100

[Sean · Accepted solution]

In the Query Editor

1) Ctrl+Select the 5 Domain Columns

2) Transform tab - Unpivot Columns - select Unpivot Only Selected Columns

3) Select the Value column - click the drop-down button in the column’s header - uncheck the Blank value

4) Ctrl+Select the 3 columns - ID, Domain1 Percentage and Value

5) Right-click on any one the selected columns’ Headers - select Remove Other Columns

6) Finally rename the Domain1 Percentage column - Domain and the Value column - Percentage

There it is!