Hello! I have an Excel table with a row showing each time an employee has received a salary increase. Using Power Query, I'd like to summarize this information to show me one record per employee, with a column showing their salary as at the end of each year (going back to 2015 or whenever they joined the company). I'm new to Power Query and struggling to get started on this one. Any advice or guidance would be greatly appreciated.
Here's what I'm working with:
Source Table
Desired Output in Power Query
Thank you!
Here's what I'm working with:
Source Table
| EMPLOYEE ID | SALARY | SALARY START DATE |
| 101 | 56,000 | 5/07/18 |
| 101 | 57,000 | 1/01/19 |
| 101 | 68,000 | 1/01/21 |
| 378 | 65,000 | 7/04/19 |
| 378 | 73,000 | 1/01/20 |
| 378 | 81,000 | 1/01/21 |
| 378 | 83,000 | 2/01/21 |
| 638 | 65,000 | 6/10/19 |
| 638 | 70,000 | 1/01/20 |
| 638 | 76,000 | 1/01/21 |
| 751 | 68,000 | 1/04/21 |
| 870 | 85,000 | 7/01/18 |
| 870 | 95,000 | 1/01/19 |
| 870 | 101,000 | 1/01/21 |
Desired Output in Power Query
| EMPLOYEE ID | 2018 SALARY | 2019 SALARY | 2020 SALARY | 2021 SALARY |
| 101 | 56,000 | 57,000 | 68,000 | 68,000 |
| 378 | - | 65,000 | 73,000 | 83,000 |
| 638 | - | 65,000 | 70,000 | 76,000 |
| 751 | - | - | - | 68,000 |
| 870 | 85,000 | 95,000 | 95,000 | 101,000 |
Thank you!
