Forum Discussion
Append two tables with overlapping date ranges
Hi,
I'm trying to create a report that will calculate the FTE of a group of staff at a particular date, as well as showing the FTE minus absences.
My problem is that I have two tables. One is a staff list, with a new row whenever there is a change in an employees data along with the start and end dates (e.g. changing job or moving location). The second is a table of absences again with a start and end date.
I ideally want to append these tables in Power Query and resolve any overlapping date ranges.
So if a given employee is in post 01/01/2020 to 31/12/2022, but is absent 01/06/2021 to 30/06/2021, I would want to end up with three rows like so:
SIP Table
| Employee | FTE | Absence | Start Date | End Date |
| A | 1 | 01/01/2020 | 31/12/2022 |
Absence Table
| Employee | Absence | Start Date | End Date |
| A | Sickness | 01/06/2021 | 30/06/2021 |
Appended Table (Ideally)
| Employee | FTE | Absence | Start Date | End Date |
| A | 1 | 01/01/2020 | 31/05/2021 | |
| A | 1 | Sickness | 01/06/2021 | 30/06/2021 |
| A | 1 | 01/07/2021 | 31/12/2022 |
In effect this would generate the dates in bold italic.
My main issue is my actual data would involve ~3000 rows and any attempts I've made have rapidly slowed the query beyond what is practical.
Any help with achieving this would be massively appreciated, thank you in advance.
Here is a pbix file with one method you could use. It will likely need to be amended once additional employees are introduced but the general idea should work.
5 Replies
Here is a pbix file with one method you could use. It will likely need to be amended once additional employees are introduced but the general idea should work.
See this solution
let Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYgUgNjDUByIjAyMDIMfYUN/QCMQxUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, FTE = _t, Absence = _t, #"Start Date" = _t, #"End Date" = _t]), ChangedType_T1 = Table.TransformColumnTypes(Table1,{{"Employee", type text}, {"FTE", Int64.Type}, {"Absence", type text}, {"Start Date", type date}, {"End Date", type date}}), Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQrOTM7OSy0uBjINDPUNzPSNDIwMgRxjAzgnNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Absence = _t, #"Start Date" = _t, #"End Date" = _t]), ChangedType_T2 = Table.TransformColumnTypes(Table2,{{"Employee", type text}, {"Absence", type text}, {"Start Date", type date}, {"End Date", type date}}), Custom1 = ChangedType_T2 & ChangedType_T1, #"Grouped Rows" = Table.Group(Custom1, {"Employee"}, {"Count", each [a=Table.Buffer(Table.AddIndexColumn(Table.Sort(_,{{"Start Date", Order.Ascending}, {"End Date", Order.Ascending}}),"i")),b=Table.AddColumn(a,"New EndDate",(x)=>try a[Start Date]{x[i]+1}-#duration(1,0,0,0) otherwise x[End Date])][b]}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Absence", "Start Date", "End Date", "FTE", "i", "New EndDate"}, {"Absence", "Start Date", "End Date", "FTE", "i", "New EndDate"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"End Date", "i"}) in #"Removed Columns"If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!
Thank you for the help, it's hugely appreciated. At the moment the above solution from jgeddes is working for me. But I may revisit your method
