Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Overlapping Time Intervals

Hello Everyone   I have been struggling with a problem involving overlapping times and dates. I have a very large document with a ton of potential time clock overlapps. I have tried the solutions l...
Need Help
  • ThxAlot's avatar
    ThxAlot
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZJBDoQgDEWvQliT2BaYUa5ivAfHn1EmEyzSdme+75VC/r57hJjf0QWHC+JCQOSD/3+7tQDcgq0Q+COo4sX1AcI5ymA2kCW2QxvIkmRVE1PRujAOC6N1YRwWpoI29QJZQtmo8rvG7q7rJvXhe8SsD5Io90EylT4oqtQHRZX6IKpyHxR17INRlfugqNM+1FrPPw/wjaYZHZ/onCd0+r1cF73ms/M4u9HHBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Partition.DupMeas_Payroll_ID_Date = _t, data.Partition.StartTimeF = _t, data.Partition.EndTimeF = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Partition.DupMeas_Payroll_ID_Date", type text}, {"data.Partition.StartTimeF", type datetime}, {"data.Partition.EndTimeF", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Partition.DupMeas_Payroll_ID_Date", Order.Ascending}, {"data.Partition.StartTimeF", Order.Ascending}}),
    Grouped = Table.Group(#"Sorted Rows", Table.ColumnNames(Source), {"grp", each _}, 0, (x,y) => Byte.From(x[Partition.DupMeas_Payroll_ID_Date] <> y[Partition.DupMeas_Payroll_ID_Date] or y[data.Partition.StartTimeF] >= x[data.Partition.EndTimeF])),
    Selected = Table.Combine(List.Select(Grouped[grp], each Table.RowCount(_) > 1))
in
    Selected

ThxAlot's avatar
ThxAlot
Icon for Super User rankSuper User
3 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZJBDoQgDEWvQliT2BaYUa5ivAfHn1EmEyzSdme+75VC/r57hJjf0QWHC+JCQOSD/3+7tQDcgq0Q+COo4sX1AcI5ymA2kCW2QxvIkmRVE1PRujAOC6N1YRwWpoI29QJZQtmo8rvG7q7rJvXhe8SsD5Io90EylT4oqtQHRZX6IKpyHxR17INRlfugqNM+1FrPPw/wjaYZHZ/onCd0+r1cF73ms/M4u9HHBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Partition.DupMeas_Payroll_ID_Date = _t, data.Partition.StartTimeF = _t, data.Partition.EndTimeF = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Partition.DupMeas_Payroll_ID_Date", type text}, {"data.Partition.StartTimeF", type datetime}, {"data.Partition.EndTimeF", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Partition.DupMeas_Payroll_ID_Date", Order.Ascending}, {"data.Partition.StartTimeF", Order.Ascending}}),
    Grouped = Table.Group(#"Sorted Rows", Table.ColumnNames(Source), {"grp", each _}, 0, (x,y) => Byte.From(x[Partition.DupMeas_Payroll_ID_Date] <> y[Partition.DupMeas_Payroll_ID_Date] or y[data.Partition.StartTimeF] >= x[data.Partition.EndTimeF])),
    Selected = Table.Combine(List.Select(Grouped[grp], each Table.RowCount(_) > 1))
in
    Selected