Please delete this post

[Mark-JZ-Yeap]

I am unable to delete this post. Please assist.

[BA_Pete · Accepted solution]

Hi @Mark-JZ-Yeap ,

Try this M code (not DAX) in a new blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY5LEsAgCEPvwtoZAv7aszje/xoVtY62O5IXAqWQkCO9GJkVqiYi4zbhm6DqCnXXb64HixwRG6AsWO4EYcQRXyC/+jiP6orgG0ljUXSrD51kOxz3d1pZJ5eRdO5QrQ8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, DOJ = _t, DOL = _t, ContinuedFor = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"DOJ", type date}, {"DOL", type date}, {"ContinuedFor", Int64.Type}, {"Employee ID", Int64.Type}}),

// Relevant steps from here =====>
    mergeOnSelf = Table.NestedJoin(chgTypes, {"Employee ID"}, chgTypes, {"ContinuedFor"}, "nextEmp", JoinKind.LeftOuter),
    expandDOJ = Table.ExpandTableColumn(mergeOnSelf, "nextEmp", {"DOJ"}, {"DOL_Updated"}),
    repDOL_Updated = Table.ReplaceValue(expandDOJ, each [DOL_Updated], each if [DOL_Updated] > [DOL] then Date.EndOfMonth(Date.AddMonths([DOL_Updated], -1)) else null, Replacer.ReplaceValue,{"DOL_Updated"})
in
    repDOL_Updated

Summary:

mergeOnSelf = Left outer merge the table on itself as [Employee ID] = [ContinuedFor].

expandDOJ = Expand the [DOJ] column from the nested table column (I also renamed it to DOL_Updated at the same time).

repDOL_Updated = Perform a conditional replace on the [DOL_Updated] column applying the required logic.

Example code turns this:

...into this:

Pete