Forum Discussion
Getting End Date from The Row With Index - 1
I have a table similar to the following which lists job titles for different workers:
| WORKER_ID | EFFECTIVE_DATE | DESCRIPTION |
| 1 | 01/03/2024 | C |
| 1 | 01/02/2024 | A |
| 1 | 01/01/2024 | B |
| 2 | 01/01/2025 | B |
| 2 | 01/02/2025 | A
|
I want to end up with the same table with an END_DATE column which is defined by the effective date of the job title which occured after the 'current' one. For example
| WORKER_ID | EFFECTIVE_DATE | DESCRIPTION | END_DATE |
| 1 | 01/03/2024 | C | |
| 1 | 01/02/2024 | A | 01/03/2024 |
| 1 | 01/01/2024 | B | 01/02/2024 |
| 2 | 01/01/2025 | B | 01/02/2025 |
| 2 | 01/02/2025 | A |
I've got my table to stage where it's grouped by WORKER_ID with a Index e.g
| WORKER_ID | EFFECTIVE_DATE | DESCRIPTION | INDEX |
| 1 | 01/03/2024 | C | 1 |
| 1 | 01/02/2024 | A | 2 |
| 1 | 01/01/2024 | B | 3 |
How do I add a custom column per WORKER_ID group which will give the effective date of the row with the index - 1?
Thank you
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1Dcw1jcyMDIBcpyVYnUQwkYwYUcUYUOYsBNY2AhZ2BRD2AgmDDQkFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WORKER_ID = _t, EFFECTIVE_DATE = _t, DESCRIPTION = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"EFFECTIVE_DATE", type date}},"en-GB"), #"Added Custom" = Table.AddColumn(#"Changed Type", "END_DATE", (k)=> List.Min(Table.SelectRows(#"Changed Type",each [WORKER_ID]=k[WORKER_ID] and [EFFECTIVE_DATE]>k[EFFECTIVE_DATE])[EFFECTIVE_DATE]),type date) in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
3 Replies
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1Dcw1jcyMDIBcpyVYnUQwkYwYUcUYUOYsBNY2AhZ2BRD2AgmDDQkFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WORKER_ID = _t, EFFECTIVE_DATE = _t, DESCRIPTION = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"EFFECTIVE_DATE", type date}},"en-GB"), #"Added Custom" = Table.AddColumn(#"Changed Type", "END_DATE", (k)=> List.Min(Table.SelectRows(#"Changed Type",each [WORKER_ID]=k[WORKER_ID] and [EFFECTIVE_DATE]>k[EFFECTIVE_DATE])[EFFECTIVE_DATE]),type date) in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
Hi Anonymous
Another possibility
let
Source = Table.NestedJoin(Your_Source, {"WORKER_ID"}, Your_Source, {"WORKER_ID"}, "END_DATE", JoinKind.Inner),
End_Date = Table.ReplaceValue(
Source,
each [EFFECTIVE_DATE],
each [END_DATE][EFFECTIVE_DATE],
(x,y,z) => List.Min(List.Select(z, each _ > y)),
{"END_DATE"})
in
End_DateStéphane
