Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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!
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!
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |