Use Power Query to Subtract time frames form a complete day

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.