Calculate differences by category in power query

[Anonymous]

Hi!

I have a dataset similar to the one below, where each row describes a salary change for a given employee. If the end date is 9999.12.31, it is the current amount. 

I need an additional column that contains the increase amount (if there was an increase, so the employee appears more than once in the table). Eg. for Mike Peterson it's $255 000 in the first row, and it's 0 where there's been no increase or if it's the first time the given name appears.

Can you advise how to do that in power query?

Thanks a lot!

ID	Name	Effective Date	End Date	Amount
89765	Mike Peterson	2022.07.01	9999.12.31	$1 106 000
89765	Mike Peterson	2022.01.01	2022.06.30	$851 000
89765	Mike Peterson	2020.08.01	2021.12.31	$774 000
59705755	John Smith	2021.12.13	9999.12.31	$550 000
59706448	Jack London	2022.07.01	9999.12.31	$350 000
59704850	Sample Peter	2021.02.15	9999.12.31	$610 000
466490	Amber Johnson	2022.07.01	9999.12.31	$519 000
466490	Amber Johnson	2022.01.01	2022.06.30	$462 000
466490	Amber Johnson	2021.01.01	2021.12.31	$440 000
98765	Barbara Smith	2020.04.14	2020.12.31	$300 000
98765	Barbara Smith	2021.01.01	9999.12.31	$330 000

[Anonymous]

Perfect, thanks a lot!

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFNi8IwFEX/SiguS3gveS8fS10ODgy4FBdRC0ptO1T/P5PQ6TSjQtvdXZzTd3P3+8J5a7goi89rXYmv6lH1966NWYFSEqwEjMHHT6KSOoUVlgimBABRHMo5Aw6GIRipIRkc4yIeJLg/HqcLrKWJZ2+BLSfFR3dpxa65Pi4ZgvqlATP85w2RS3w41WLbtee5J9DPAnKcmu1C8337bTGeAPEEfjEYzAxkDPnEr5tj1YtUY3YFRr9Y8HYEMmqZAHNBtgJRVsG7YcVN6I+hD/kKcUWSSGOYHhGW8dP/n0bQI3/4AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, #"Effective Date" = _t, #"End Date" = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Effective Date", type date}, {"End Date", type date}, {"Amount", Currency.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "OIndex", 0, 1, Int64.Type),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Index",{{"ID", Order.Ascending}, {"End Date", Order.Ascending}})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"AllRows", each Table.AddIndexColumn(_,"Index",0,1)}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
        DummyTbl = [AllRows]
        in
        Table.AddColumn(DummyTbl, "Salary Change", each if [Index]=0 then null else [Amount]-DummyTbl[Amount]{[Index]-1})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Effective Date", "End Date", "Amount", "OIndex", "Salary Change"}, {"Name", "Effective Date", "End Date", "Amount", "OIndex", "Salary Change"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Custom",{{"OIndex", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows1",{"OIndex"})
in
    #"Removed Columns1"

 👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data