Forum Discussion
Get End Date by Next Action Date - Get Continuous Date Range by Category
Hello,
I have a question about generating an end date by only using effective dates. I can easily do this by grouping, and I have also seen how to make the 0 Index column and 1 Index column to merge a table with itself.
However, I have a specific issue causing me to have overlapping dates. It has to do with a position that changes it's manager back to a previous position that it already had.
Example:
Position "ABC123" reports to manager "BBB111" from 25-Feb-2013 until it was changed to report to manager “CCC222” on 01-Jan-2018. The position "ABC123" will continue reporting to "CCC222" from 01-Jan-2018 until 07-Sept-2021 when the reports to manager is changed back to "BBB111" where it stays reporting until currently.
| Position ID | Reports To | Effective Date | Effective End Date |
| ABC123 | BBB111 | 25-Feb-2013 | 31-Dec-2017 |
| ABC123 | CCC222 | 01-Jan-2018 | 06-Sept-2021 |
| ABC123 | BBB111 | 07-Sept-2021 |
A couple of issues with the data is that I’m not able to pull only when the manager changes. I have to pull all the dates for any type of changes to the position. So there are dates involved that I don’t care about. Like a position may have 3 rows and dates of various changes, but it still reports to the same manager before changing to another manager. I only want the range that the position reports to a manager.
I have a list of employee positions and the history of changes to the postion as well as the manager position it is reporting to. I want to get the date ranges a position is active and which manager position it is reporting to. I am trying to have a reference for a historical organization chart.
The ultimate purpose of this is so I can reference this table using a date, and it will tell me which positions were active that date and who did it report to at the time.
Here is a photo of what my source data looks like and what I want it to end up looking like. I will attach a file that has the data.
Source and Final Output
Here is a smaller sample data in a table for reference.
| Position ID | Reports To Position | Effective Date |
| 123456 | 831502 | 2/25/2013 |
| 123456 | 831502 | 4/6/2015 |
| 123456 | 831502 | 5/8/2015 |
| 123456 | 829162 | 1/1/2018 |
| 123456 | 829162 | 5/11/2020 |
| 123456 | 831502 | 9/7/2021 |
| 123456 | 831502 | 12/18/2023 |
| 222333 | 832612 | 6/25/2013 |
| 222333 | 832612 | 4/19/2015 |
| 222333 | 832612 | 4/21/2015 |
What the table should look like:
| Position ID | Reports To Position | Effective Date | Effective End Date |
| 123456 | 831502 | 2/25/2013 | 12/31/2017 |
| 123456 | 829162 | 1/1/2018 | 9/6/2021 |
| 123456 | 831502 | 9/7/2021 | 12/18/2023 |
| 222333 | 832612 | 6/25/2013 | 4/21/2015 |
However I could do this in Power BI Desktop or in Excel using M Power Query.
Thanks for the assistance
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZS9bsMgFIVfxfKcUi4/BuZ06dQqaqcoA0qsxFJlRzbJ0KcvEONS1TF2JA8gnQ8u59zr7TYHQhkv8lUuKXBM7IJwhAkiGKjdbMqnt/ao6+pbm6qpnzdlZ9rL3lzaMt+ttnkM4gJh5kBuN+9NVzkiWzf1tWw7u/wPSIR5AD4q81Vm65Ouj79HEwWFVwKyn1VKX9O5aU2XmeaeHOB2MMFxJS/a6OzzfNBmpHaBsHIEzCVAInA2ETpFMMY4d8+T7FF72VJ7h5twyl5KCwj2SqcsJu0NchB9JSJpVriA9oHAWClSSqWU02MOTDxkUrioQIAdyCZfEsnneRoKs0751L1Tk0+PCN9Zf80aiy0KY+j10dgi5a0YmSomJnByMAghlFI/UD23OI8AgooMXgISGEvmtdZ7U11toQdPAIAQzlgpHh2vASR9Tuye7WLpIIrZ/7mgpH0+6fYqgHN/tuq7JTmLA2GdFbO6JiJSXbP7AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Position ID" = _t, #"Reports to Position" = _t, #"Effective Date" = _t, #"Workforce Action Reason Description" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Position ID", Int64.Type}, {"Reports to Position", Int64.Type}, {"Effective Date", type date}, {"Workforce Action Reason Description", type text}}), #"Filled Down" = Table.FillDown(#"Changed Type",{"Position ID"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"Position ID"}, {{"Count", (x)=> Table.AddColumn( Table.AddIndexColumn( x,"Index",0,1),"nextDate", each [ a=List.Buffer( x[Effective Date]&{Date.AddDays( List.Max( x[Effective Date]),1)}), b= List.Range( a,[Index]+1,1){0}, c = Date.AddDays(b,-1) ][c] ) }}), #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}), #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Position ID", "Reports to Position", "Effective Date", "Workforce Action Reason Description", "Index", "nextDate"}, {"Position ID", "Reports to Position", "Effective Date", "Workforce Action Reason Description", "Index", "nextDate"}), #"Grouped Rows1" = Table.Group(#"Expanded Count", {"Position ID", "Reports to Position"}, {{"MinDate", each List.Min([Effective Date]), type nullable date}, {"MaxDate", each List.Max([nextDate]), type nullable date}},GroupKind.Local) in #"Grouped Rows1"
3 Replies
- Ahmedx
Super User
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZS9bsMgFIVfxfKcUi4/BuZ06dQqaqcoA0qsxFJlRzbJ0KcvEONS1TF2JA8gnQ8u59zr7TYHQhkv8lUuKXBM7IJwhAkiGKjdbMqnt/ao6+pbm6qpnzdlZ9rL3lzaMt+ttnkM4gJh5kBuN+9NVzkiWzf1tWw7u/wPSIR5AD4q81Vm65Ouj79HEwWFVwKyn1VKX9O5aU2XmeaeHOB2MMFxJS/a6OzzfNBmpHaBsHIEzCVAInA2ETpFMMY4d8+T7FF72VJ7h5twyl5KCwj2SqcsJu0NchB9JSJpVriA9oHAWClSSqWU02MOTDxkUrioQIAdyCZfEsnneRoKs0751L1Tk0+PCN9Zf80aiy0KY+j10dgi5a0YmSomJnByMAghlFI/UD23OI8AgooMXgISGEvmtdZ7U11toQdPAIAQzlgpHh2vASR9Tuye7WLpIIrZ/7mgpH0+6fYqgHN/tuq7JTmLA2GdFbO6JiJSXbP7AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Position ID" = _t, #"Reports to Position" = _t, #"Effective Date" = _t, #"Workforce Action Reason Description" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Position ID", Int64.Type}, {"Reports to Position", Int64.Type}, {"Effective Date", type date}, {"Workforce Action Reason Description", type text}}), #"Filled Down" = Table.FillDown(#"Changed Type",{"Position ID"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"Position ID"}, {{"Count", (x)=> Table.AddColumn( Table.AddIndexColumn( x,"Index",0,1),"nextDate", each [ a=List.Buffer( x[Effective Date]&{Date.AddDays( List.Max( x[Effective Date]),1)}), b= List.Range( a,[Index]+1,1){0}, c = Date.AddDays(b,-1) ][c] ) }}), #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}), #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Position ID", "Reports to Position", "Effective Date", "Workforce Action Reason Description", "Index", "nextDate"}, {"Position ID", "Reports to Position", "Effective Date", "Workforce Action Reason Description", "Index", "nextDate"}), #"Grouped Rows1" = Table.Group(#"Expanded Count", {"Position ID", "Reports to Position"}, {{"MinDate", each List.Min([Effective Date]), type nullable date}, {"MaxDate", each List.Max([nextDate]), type nullable date}},GroupKind.Local) in #"Grouped Rows1" - dufoq3
Community Champion
Hi wskinner, another solution:
Result
let fnShift = (tbl as table, col as text, shift as nullable number, optional newColName as text, optional _type as type) as table => //v 3. parametri zadaj zaporne cislo ak chces posunut riadky hore, kladne ak dole, 4. je nepovinny (novy nazov stlpca), 5. je nepovinny typ let a = Table.Column(tbl, col), b = if shift = 0 or shift = null then a else if shift > 0 then List.Repeat({null}, shift) & List.RemoveLastN(a, shift) else List.RemoveFirstN(a, shift * -1) & List.Repeat({null}, shift * -1), c = Table.FromColumns(Table.ToColumns(tbl) & {b}, Table.ColumnNames(tbl) & ( if newColName <> null then {newColName} else if shift = 0 then {col & "_Duplicate"} else if shift > 0 then {col & "_PrevValue"} else {col & "_NextValue"} )), d = Table.TransformColumnTypes(c, {List.Last(Table.ColumnNames(c)), if _type <> null then _type else type any}) in d, fnDates = (myTable as table)=> [ // _Detail = GroupedRows{[#"Position ID"=123456]}[All], _Detail = myTable, _GroupedRowsLocal = Table.Group(_Detail, {"Position ID", "Reports To Position"}, {{"All", each _, type table}, {"Min", each List.Min([Effective Date]), type date}, {"Max", each List.Max([Effective Date]), type date}}, GroupKind.Local), _fnShiftMin = fnShift(_GroupedRowsLocal, "Min", -1, null, type date), _Ad_EffectiveEndDate = Table.AddColumn(_fnShiftMin, "Effective End Date", each if [Min_NextValue] <> null then Date.AddDays([Min_NextValue], -1) else [Max], type date), _RenamedColumn = Table.RenameColumns(_Ad_EffectiveEndDate,{{"Min", "Position#(tab)Effective Date"}}), _RemovedOtherColumns = Table.SelectColumns(_RenamedColumn,{"Position ID", "Position#(tab)Effective Date", "Effective End Date"}) ][_RemovedOtherColumns], Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc/RCcQwDAPQXfJdUCTHvmSW0v3XaN07KOWSP8MTtrzvhbLmUbbSjV51DYIcqrRybDNviGRfsKPPWIORTDC5L9jBdNXF9oFPMhdMgXle3/KSzOwOKJiBeD337w0cT/2Ziz8/Tg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Position ID" = _t, #"Reports To Position" = _t, #"Effective Date" = _t]), ChangedTypeUS = Table.TransformColumnTypes(Source,{{"Position ID", Int64.Type}, {"Reports To Position", Int64.Type}, {"Effective Date", type date}}, "en-US"), GroupedRows = Table.Group(ChangedTypeUS, {"Position ID"}, {{"All", fnDates, type table}}), Combined = Table.Combine(GroupedRows[All]) in Combined
