if there is a new date for specific name, end date is new date m query

[eliasayyy]

hello guys the title is a bit confusing but i will try to explain,
i have employee names i na table, with there effective date and pay

lets take example that next month, name B will quit and name A will stay but get a raise and name C will join: here is example of the table:

Name	Date	Pay
Name A	12/1/2022	500
Name A	1/1/2023	700
Name B	12/1/2022	400
Name B	1/1/2023	0
Name C	1/1/2023	400
Name C	1/16/2023	500

what i want is in power query, i want to add a new column that is called end date, that says something like " if the name has a new date(max) this will be the end date for the previous period of the name, but if pay is 0, then same date and if its the latest day of the name, then end date is date.localnow()"

so desired result would be:

Name	Date	Pay	End Date
Name A	12/1/2022	500	1/1/2023
Name A	1/1/2023	700	Today
Name B	12/1/2022	400	1/1/2023
Name B	1/1/2023	0	1/1/2023
Name C	1/1/2023	400	1/3/2023
Name C	1/3/2023	500	Today

is it possible?

[Anonymous · Accepted solution]

Use Following PQ with 3 tables

PQ1: EmployeesPay Source

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00jfUNzIwMgKyTQ0MlGJ1oOIQYWMg0xwq7ISm3ARZHKEcIuiMKghTCxE2hgmDbYwFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Pay = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}, {"Pay", Int64.Type}})
in
#"Changed Type"

PQ2: EmployeesPay MaxDate

let
Source = #"EmployeesPay Source",
#"Grouped Rows" = Table.Group(Source, {"Name"}, {{"MaxDate", each List.Max([Date]), type nullable date}})
in
#"Grouped Rows"

PQ3: EmployeesPay FinalTable

let
Source = #"EmployeesPay Source",
#"Merged Queries" = Table.NestedJoin(Source, {"Name"}, #"EmployeesPay MaxDate", {"Name"}, "EmployeesPay MaxDate", JoinKind.LeftOuter),
#"Expanded EmployeesPay MaxDate" = Table.ExpandTableColumn(#"Merged Queries", "EmployeesPay MaxDate", {"MaxDate"}, {"MaxDate"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded EmployeesPay MaxDate", "TerminateFlag", each if [Pay] = 0 then "Yes" else "No"),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "isMaxDateRow", each if [Date] = [MaxDate] then "Yes" else "No"),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"TerminateFlag", type text}, {"isMaxDateRow", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "NewMaxDate", each if [TerminateFlag] = "Yes" or [isMaxDateRow] = "No" then [MaxDate] else DateTime.LocalNow()),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"NewMaxDate", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"MaxDate", "TerminateFlag", "isMaxDateRow"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewMaxDate", "MaxDate"}})
in
#"Renamed Columns"

Result