Transforming and summarizing salary data with Power Query in Excel

[Ozzyy]

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

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!

 

[alansidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMPLOYEE ID", Int64.Type}, {"SALARY", Int64.Type}, {"SALARY START DATE", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([SALARY START DATE]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Year",{"SALARY START DATE"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Year", type text}}, "en-US")[Year]), "Year", "SALARY", List.Sum),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"EMPLOYEE ID", "2018", "2019", "2020", "2021"})
in
    #"Reordered Columns"

 

[Ozzyy]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"EMPLOYEE ID", Int64.Type}, {"SALARY", Int64.Type}, {"SALARY START DATE", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([SALARY START DATE]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Year",{"SALARY START DATE"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Year", type text}}, "en-US")[Year]), "Year", "SALARY", List.Sum),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"EMPLOYEE ID", "2018", "2019", "2020", "2021"})
in
    #"Reordered Columns"

Thanks for your help!

This is very close. There are just two things I'm noticing aren't quite working:
1. If an employee has more than one salary increase in a year (like employee id 378), I'd like to just see the latest one from that year, rather than adding them together
2. In years where an employee didn't get a salary increase, I'm looking for it to carry across the value from their previous year's increase (rather than null)

Current output from your code:

EMPLOYEE ID	2018	2019	2020	2021
101	56,000	57,000		68,000
378		65,000	73,000	164,000
638		65,000	70,000	76,000
751				68,000
870	85,000	95,000		101,000

Desired output:

EMPLOYEE ID	2018	2019	2020	2021
101	56,000	57,000	57,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

Is this possible to do? And thanks again; I really appreciate the help.

 

[alansidman]

Maybe someone else can make it happen. I don't see a viable solution using PQ for what you are asking. You may need to go with a VBA solution instead of pivoting data in PQ.

 

[Bo_Ry]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Year = Table.TransformColumns(Source,{{"SALARY START DATE", Date.Year, Int64.Type}}),
    Grouped = Table.Group(Year, {"EMPLOYEE ID"}, {{"Count", each 
        let a = Table.FromColumns({[EMPLOYEE ID],[SALARY], [SALARY START DATE] & List.Distinct(Year[SALARY START DATE])}) 
        in Table.FillDown(Table.Sort(a,{"Column3",0}),{"Column2"})
    }}),
    Expanded = Table.ExpandTableColumn(Grouped, "Count", {"Column2", "Column3"}, {"Column2", "Column3"}),
    Pivoted = Table.Pivot(Table.TransformColumnTypes(Expanded, {{"Column3", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Expanded, {{"Column3", type text}}, "en-US")[Column3]), "Column3", "Column2", List.Max)
in
    Pivoted

EMPLOYEE ID	SALARY	SALARY START DATE		EMPLOYEE ID	2018	2019	2020	2021
101	56000	07-May-18		101	56,000	57,000	57,000	68,000
101	57000	01-Jan-19		378		65,000	73,000	83,000
101	68000	01-Jan-21		638		65,000	70,000	76,000
378	65000	04-Jul-19		751				68,000
378	73000	01-Jan-20		870	85,000	95,000	95,000	101,000
378	81000	01-Jan-21
378	83000	01-Feb-21
638	65000	10-Jun-19
638	70000	01-Jan-20
638	76000	01-Jan-21
751	68000	04-Jan-21
870	85000	01-Jul-18
870	95000	01-Jan-19
870	101000	01-Jan-21

 

[Ozzyy]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Year = Table.TransformColumns(Source,{{"SALARY START DATE", Date.Year, Int64.Type}}),
    Grouped = Table.Group(Year, {"EMPLOYEE ID"}, {{"Count", each
        let a = Table.FromColumns({[EMPLOYEE ID],[SALARY], [SALARY START DATE] & List.Distinct(Year[SALARY START DATE])})
        in Table.FillDown(Table.Sort(a,{"Column3",0}),{"Column2"})
    }}),
    Expanded = Table.ExpandTableColumn(Grouped, "Count", {"Column2", "Column3"}, {"Column2", "Column3"}),
    Pivoted = Table.Pivot(Table.TransformColumnTypes(Expanded, {{"Column3", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Expanded, {{"Column3", type text}}, "en-US")[Column3]), "Column3", "Column2", List.Max)
in
    Pivoted

Thank you, this is great progress. The only outstanding issue with it is I need to ensure that, for years where there is more than one salary change for an employee, it picks up the latest one, rather than just the highest salary for them that year. The logic in your code is very close to what I need: the only scenario where it isn't quite right is if someone's salary decreases in a year (e.g., if they move from full-time to part-time). Here's a revised data set and what I'm seeing:

Source Table:

EMPLOYEE ID	SALARY	SALARY START DATE
101	56,000	May 7, 2018
101	57,000	January 1, 2019
101	68,000	January 1, 2021
378	65,000	July 4, 2019
378	73,000	January 1, 2020
378	81,000	January 1, 2021
378	40,000	February 1, 2021
638	65,000	June 10, 2019
638	70,000	January 1, 2020
638	76,000	January 1, 2021
751	68,000	January 4, 2021
870	85,000	July 1, 2018
870	95,000	January 1, 2019
870	101,000	January 1, 2021

OUTPUT FROM YOUR CODE						DESIRED OUTPUT
EMPLOYEE ID	2018	2019	2020	2021		EMPLOYEE ID	2018	2019	2020	2021
101	56,000	57,000	57,000	68,000		101	56,000	57,000	57,000	68,000
378		65,000	73,000	81,000		378		65,000	73,000	40,000
638		65,000	70,000	76,000		638		65,000	70,000	76,000
751				68,000		751				68,000
870	85,000	95,000	95,000	101,000		870	85,000	95,000	95,000	101,000

Any thoughts on this last piece? Thanks again!

 

[Ozzyy]

Maybe someone else can make it happen. I don't see a viable solution using PQ for what you are asking. You may need to go with a VBA solution instead of pivoting data in PQ.

Interesting thought, thanks. Would this be complicated to tackle with VBA (and could the resulting table be loaded into Power Query)?

 

[Bo_Ry]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AYear = Table.AddColumn(Source, "Year", each Date.Year([SALARY START DATE])),
    Grouped = Table.Group(AYear, {"EMPLOYEE ID"}, {{"Count", each
        let a = Table.FromColumns({[EMPLOYEE ID],[SALARY], [SALARY START DATE], [Year] & List.Distinct(AYear[Year])})
        in Table.Sort(Table.Distinct(Table.FillUp(Table.Sort(a,{{"Column4",1},{"Column3",1}}),Table.ColumnNames(a)),"Column4"),{"Column4",0})
    }}),
    Expanded = Table.ExpandTableColumn(Grouped, "Count", {"Column2", "Column4"}),
    Pivoted = Table.Pivot(Table.TransformColumnTypes(Expanded, {{"Column4", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Expanded, {{"Column4", type text}}, "en-US")[Column4]), "Column4", "Column2")
in
    Pivoted

 

[Ozzyy]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AYear = Table.AddColumn(Source, "Year", each Date.Year([SALARY START DATE])),
    Grouped = Table.Group(AYear, {"EMPLOYEE ID"}, {{"Count", each
        let a = Table.FromColumns({[EMPLOYEE ID],[SALARY], [SALARY START DATE], [Year] & List.Distinct(AYear[Year])})
        in Table.Sort(Table.Distinct(Table.FillUp(Table.Sort(a,{{"Column4",1},{"Column3",1}}),Table.ColumnNames(a)),"Column4"),{"Column4",0})
    }}),
    Expanded = Table.ExpandTableColumn(Grouped, "Count", {"Column2", "Column4"}),
    Pivoted = Table.Pivot(Table.TransformColumnTypes(Expanded, {{"Column4", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Expanded, {{"Column4", type text}}, "en-US")[Column4]), "Column4", "Column2")
in
    Pivoted

That did it! Thanks so much; I was really stumped on this and your solution is perfect. Much appreciated!