Forum Discussion
End date for each start date by person
Hello
I am trying to add a custom column that will give me the "Date Effective To" for each person below, depending on the start date of the next grade. Data I am working with:
| Name | ID | Date Effective From | Grade |
| Joe Bloggs | A001 | 01/01/2025 | Z100 |
| Joe Bloggs | A001 | 01/06/2025 | Z101 |
| Joe Bloggs | A001 | 01/01/2026 | Z102 |
| Joe Bloggs | A001 | 12/12/2026 | Z103 |
| Jill Joggs | A002 | 01/01/2024 | Z101 |
| Jill Joggs | A002 | 06/06/2024 | Z102 |
| Jill Joggs | A002 | 01/01/2025 | Z103 |
| Jill Joggs | A002 | 01/01/2026 | Z105 |
Requirement is to have an extra column with the date effective to, considering the person (by ID rather than name) and their next start date.
Thank you very Much
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVXDKyU9PL1bSUXI0MDAEUgaG+kBkZGBkCuREGRoYKMXq4FZphqTSEK9KsJlmEJVGOFUaGukDEUKlMURlZk6OghdCpRGymSYotmNTaQZ1pwmK7fjMNCXadqg7TZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t, #"Date Effective From" = _t, Grade = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Effective From", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date Effective To", (k)=> List.Min(Table.SelectRows(#"Changed Type",each [ID]=k[ID] and [Date Effective From]>k[Date Effective From])[Date Effective From])-#duration(1,0,0,0),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.
2 Replies
- lbendlin
Super User
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVXDKyU9PL1bSUXI0MDAEUgaG+kBkZGBkCuREGRoYKMXq4FZphqTSEK9KsJlmEJVGOFUaGukDEUKlMURlZk6OghdCpRGymSYotmNTaQZ1pwmK7fjMNCXadqg7TZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, ID = _t, #"Date Effective From" = _t, Grade = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Effective From", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date Effective To", (k)=> List.Min(Table.SelectRows(#"Changed Type",each [ID]=k[ID] and [Date Effective From]>k[Date Effective From])[Date Effective From])-#duration(1,0,0,0),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.
- Omid_Motamedise
Hi Anonymous
Your question is not clear for me, can you provide your target table?
