Append two tables with overlapping date ranges

[FrankJust]

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.

[Omid_Motamedise]

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!

[FrankJust]

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 

[jgeddes · Accepted solution]

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.

[FrankJust]

This looks like it works for me. Thank you again!

[FrankJust]

Thank you, I'll take a look at this tomorrow. Appreciate the help!