Forum Discussion
Timesheets Overlapping
HI I have columns
Employee ID, Start Date, Start Time, Finish Time
I am trying to identify overlapping but this is bringing up false every time.
(Table as table) as table =>
let
AddIndex = if Table.HasColumns(Table, "Index")
then Table
else Table.AddIndexColumn(Table, "Index", 1, 1, Int64.Type),
AddCheckColumn = Table.AddColumn(AddIndex, "Overlap", each List.AnyTrue(
List.Transform(
Table.SelectRows(AddIndex, each _[Index] <> [Index] and
_[Employee ID] = [Employee ID] and
_[Start Date] = [Start Date] and
(
(_[Start Time] < [Finish Time] and _[Finish Time] > [Start Time]) or
(_[Finish Time] > [Start Time] and _[Finish Time] <= [Finish Time]) or
(_[Start Time] <= [Start Time] and _[Finish Time] >= [Finish Time])
))[Index], each true))),
RemoveIndex = if Table.HasColumns(AddCheckColumn, "Index")
then Table.RemoveColumns(AddCheckColumn, {"Index"})
else AddCheckColumn
in
RemoveIndex
How can i get correct results?
Thanks
Hi Babycakes_00
lets check the logic then I provide the solution for you. Consider tow rows with starting and ending time as [S1 E1] and [S2 E2] . Thes two rows have overlap if any of the below situation happen.
S2 < S1 <E2
S2<E1< E2
S1 < S2 <E1
S1<E2< E1
additionally you cant (usually) use two nested each _
so by knowing this, use the below formula.
(Table as table) as table =>
let
AddIndex =
if Table.HasColumns(Table, "Index") then
Table
else
Table.AddIndexColumn(Table, "Index", 1, 1, Int64.Type),
AddCheckColumn = Table.AddColumn(
AddIndex,
"Overlap",
each
Table.RowCount(
Table.SelectRows(
AddIndex,
(x)=> x[Employee ID] = _[Employee ID]
and ( x[Start Date] = [Start Date] )
and (
(x[Start Time] < _[Start Time] and x[Finish Time] > [Start Time])
or (x[Start Time] < _[Finish Time] and x[Finish Time] > [Finish Time])
or (_[Start Time] < x[Start Time] and _[Finish Time] > x[Start Time])
or (_[Start Time] < x[Finish Time] and _[Finish Time] > x[Finish Time])
)
)
)>0),
RemoveIndex =
if Table.HasColumns(AddCheckColumn, "Index") then
Table.RemoveColumns(AddCheckColumn, {"Index"})
else
AddCheckColumn
in
RemoveIndex
3 Replies
Do you whant to check each row with all the other rows and see is there any overlaping or do you whant to merge them?
Hi Babycakes_00
lets check the logic then I provide the solution for you. Consider tow rows with starting and ending time as [S1 E1] and [S2 E2] . Thes two rows have overlap if any of the below situation happen.
S2 < S1 <E2
S2<E1< E2
S1 < S2 <E1
S1<E2< E1
additionally you cant (usually) use two nested each _
so by knowing this, use the below formula.
(Table as table) as table =>
let
AddIndex =
if Table.HasColumns(Table, "Index") then
Table
else
Table.AddIndexColumn(Table, "Index", 1, 1, Int64.Type),
AddCheckColumn = Table.AddColumn(
AddIndex,
"Overlap",
each
Table.RowCount(
Table.SelectRows(
AddIndex,
(x)=> x[Employee ID] = _[Employee ID]
and ( x[Start Date] = [Start Date] )
and (
(x[Start Time] < _[Start Time] and x[Finish Time] > [Start Time])
or (x[Start Time] < _[Finish Time] and x[Finish Time] > [Finish Time])
or (_[Start Time] < x[Start Time] and _[Finish Time] > x[Start Time])
or (_[Start Time] < x[Finish Time] and _[Finish Time] > x[Finish Time])
)
)
)>0),
RemoveIndex =
if Table.HasColumns(AddCheckColumn, "Index") then
Table.RemoveColumns(AddCheckColumn, {"Index"})
else
AddCheckColumn
in
RemoveIndex
