Use Power Query to Subtract time frames form a complete day

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

This is tricky. You can take a look at my solution to find all the overlap times. To check the logic you can first remove all but the first two rows in my example table.

let
  source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jcy7CcAwEATRVsTFAu19NlEr4vpvwzhQYPuMnQ6PWUsMFgMc6k1tAtMg/Vkdkr3SrHTcdDT1s+Py3lXfNP9olpofWlFqk8wD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),

  custom = Table.AddColumn(source, "Inner", each source),
  customTypes = Table.TransformColumnTypes(custom, {{"Start", type datetime}, {"End", type datetime}}),
  sourceMultiply = Table.ExpandTableColumn(customTypes, "Inner", {"Start", "End"}, {"Inner.Start", "Inner.End"}),
  sourceMultiplyClean = Table.TransformColumnTypes(sourceMultiply, {{"Inner.Start", type datetime}, {"Inner.End", type datetime}}),
  reorder = Table.ReorderColumns(sourceMultiplyClean, {"Start", "Inner.Start", "End", "Inner.End"}),

  // Delete self-multiplied rows
  reorderClean = Table.AddColumn(reorder, "IsItself", each if ([Start] = [Inner.Start] and [End] = [Inner.End]) then true else false),
  reorderCleanFiltered = Table.SelectRows(reorderClean, each ([IsItself] = false)),

  // functions for minDate, maxDate
  minDate = (date1, date2)=> if date1 < date2 then date1 else date2,
  maxDate = (date1, date2)=> if date1 >= date2 then date1 else date2,

  // Columns for minEnd and maxStart
  minEnd = Table.AddColumn(reorderCleanFiltered,"MinEnd",  each  minDate([End],[Inner.End]), type datetime),
  maxStart = Table.AddColumn(minEnd, "MaxStart", each maxDate([Start], [Inner.Start]), type datetime ),  
  
  // Calculate time difference
  durationColumn = Table.AddColumn(maxStart, "Duration", each [MinEnd] - [MaxStart], type duration),
  durationGreaterZero = Table.SelectRows(durationColumn, each [Duration] > #duration(0, 0, 0, 0)),

  // delete doubles
  keepDistinct = Table.Distinct(durationGreaterZero, {"MinEnd","MaxStart"})  

in
 keepDistinct

You could then first calculate your durations between faults per day and then substract the overlaps for each day.

1. Add a column that holds the full source table.

2. Expand "Inner.Start" and "Inner.End"

3. Find the minimum end of each event.

4. Find the maximum start of each event.

5. Calculate duration

6. No overlap -> negative duration -> delete rows

6. Delete where the row multiplied by itself.

7. The overlap time will be produced twice, so keep only distinct.

Example as follows:

I am not completely sure if it catches all scenarios. Please test extensively.

Good day Zneta

I've interpreted your question a little differently from Ibanez2000 - so my reply may not be relevant. Nevertheless your question was interesting and perhaps what I describe may help somebody. My approach is a bit of "brute force".

• For each start and finish pair of timestamps I calculate a list of all the times from start to end (my example uses an interval of one second).
• I then take a union of all these lists. This yields a list of all fault seconds.
• I convert that list to a table of datetimes and add a date column.
• I group by date and a count of rows gives the count of fault seconds in each date.
• Finally I calculate the fault free seconds.

While it's brute force and may not perform well on very large data sets it should be robust for all cases e.g. faults straddling midnight or multiple days.

Starting from this...

I get this...

Using this code

let
  source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdBBCoAwDETRq4SuC50kjWKvUnL/a0gFwWpEt59HYNJ7EkgtsMJKLA1ogpSfVZE8R9oiXW+6EuvomG6fld+0/dEWavvQjFDLrBcSbbaN9Re90jFlfMp9Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
  #"Changed Type" = Table.TransformColumnTypes(source,{{"Start", type datetime}, {"End", type datetime}}),
  #"Added Lists of Fault Seconds" = Table.AddColumn(#"Changed Type", "Fault Periods", each List.DateTimes([Start],Duration.TotalSeconds([End]-[Start]),#duration(0,0,0,1))),
  #"Find the Union of All Fault Seconds" = List.Union( #"Added Lists of Fault Seconds"[Fault Periods] ),
  #"Converted to Table" = Table.FromList(#"Find the Union of All Fault Seconds", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Changed Type to datetime" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type datetime}}),
  #"Inserted Date" = Table.AddColumn(#"Changed Type to datetime", "Date", each DateTime.Date([Column1]), type date),
  #"Grouped by Date, Counting Fault Seconds" = Table.Group(#"Inserted Date", {"Date"}, {{"Fault Seconds", each Table.RowCount(_), Int64.Type}}),
  #"Calculate Fault Free Seconds" = Table.AddColumn(#"Grouped by Date, Counting Fault Seconds", "Fault Free Seconds", each 86400 - [Fault Seconds], Int64.Type)
in
  #"Calculate Fault Free Seconds"

Hope this helps.

Thanks for the answers I will test all suggested solutions. As requested here is the data that i have. My expected result should be a timeframe in seconds where none of these timeframes happen.
Ideally i want to have a visual Timeline that displays the time frames in red and the times inbetween the given frames in green.

Start                                              End