Forum Discussion
Unpivot Repeating Attribute Value Columns
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 |
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! :smileyhappy:
1 Reply
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! :smileyhappy: