GreenCat223
New Member
- Joined
- Mar 27, 2024
- Messages
- 5
- Office Version
- 365
- 2021
- 2019
- Platform
- Windows
Hi there!
I was wondering if someone could help explain why my data doesn't pivot in Power Query?
This is how it comes out of the system (Table 1):
This is how I want the data to look like (Table 2):
What I don't understand is why this data (Table 3) is still expanded as Table 1 when I use the pivot feature in Power Query.
I'm aware that if I remove the "Element Entry ID Value" column, prior to pivoting I will get the result I want in Table 2.
An explanation in laymen's terms would be really helpful so I can understand it better!
Thank you very much for your time.
I was wondering if someone could help explain why my data doesn't pivot in Power Query?
This is how it comes out of the system (Table 1):
| EmpNum | ProductName | InputName | EntryValue | Element Entry ID | Element Entry ID Value |
| Emp1 | Apples | Type | GrannySmith | 123 | 345 |
| Emp1 | Apples | Units | 5 | 123 | 347 |
| Emp1 | Apples | Type | Gala | 456 | 349 |
| Emp1 | Apples | Units | 3 | 456 | 351 |
| Emp1 | Apples | Type | Braeburn | 789 | 353 |
| Emp1 | Apples | Units | 1 | 789 | 355 |
| Emp1 | Apples | Type | PinkLady | 321 | 357 |
| Emp1 | Apples | Units | 7 | 321 | 359 |
| Emp2 | Oranges | Type | Satsuma | 566 | 756 |
| Emp2 | Oranges | Units | 6 | 566 | 758 |
| Emp3 | Oranges | Type | Satsuma | 788 | 555 |
| Emp3 | Oranges | Units | 6 | 788 | 557 |
| Emp4 | Pears | Units | 5 | 458 | 997 |
| Emp5 | Mango | Type | Chaunsa | 432 | 160 |
| Emp5 | Mango | Units | 7 | 432 | 162 |
| Emp5 | Mango | Season | Summer | 432 | 164 |
| Emp6 | Plum | Type | Plum | 571 | 166 |
This is how I want the data to look like (Table 2):
| EmpNum | ProductName | Type | Units | Season | Element Entry ID |
| Emp1 | Apples | GrannySmith | 5 | 123 | |
| Emp1 | Apples | Gala | 3 | 456 | |
| Emp1 | Apples | Braeburn | 1 | 789 | |
| Emp1 | Apples | PinkLady | 7 | 321 | |
| Emp5 | Mango | Chaunsa | 7 | Summer | 432 |
| Emp2 | Orange | Satsuma | 6 | 566 | |
| Emp3 | Orange | Satsuma | 6 | 788 | |
| Emp4 | Pears | 5 | 789 |
What I don't understand is why this data (Table 3) is still expanded as Table 1 when I use the pivot feature in Power Query.
| EmpNum | ProductName | Type | Element Entry ID Value | Units | Season | Element Entry ID |
| Emp1 | Apples | 351 | 3 | 456 | ||
| Emp1 | Apples | Gala | 349 | 456 | ||
| Emp1 | Apples | 355 | 1 | 789 | ||
| Emp1 | Apples | Braeburn | 353 | 789 | ||
| Emp1 | Apples | 347 | 5 | 123 | ||
| Emp1 | Apples | GrannySmith | 345 | 123 | ||
| Emp1 | Apples | 359 | 7 | 321 | ||
| Emp1 | Apples | PinkLady | 357 | 321 | ||
| Emp5 | Mango | Chaunsa | 160 | 432 | ||
| Emp5 | Mango | 162 | 7 | 432 | ||
| Emp5 | Mango | 164 | Summer | 432 | ||
| Emp3 | Oranges | 557 | 6 | 788 | ||
| Emp2 | Oranges | Satsuma | 756 | 566 | ||
| Emp3 | Oranges | Satsuma | 555 | 788 | ||
| Emp2 | Oranges | 758 | 6 | 566 | ||
| Emp4 | Pears | 997 | 5 | 458 | ||
| Emp6 | Plum | Plum | 166 | 571 |
I'm aware that if I remove the "Element Entry ID Value" column, prior to pivoting I will get the result I want in Table 2.
An explanation in laymen's terms would be really helpful so I can understand it better!
Thank you very much for your time.
