Hello everybody,
There might be an easier solution to my problem, or to not do it in Power Query at all. Any help is appreciated:
I have a Dataset with start and end times like this:
Start: 10:30 am, End: 10:33 am
Start: 10:31 am, End 10:45 am
See the picture below.
Many of these timeframes overlap each other. I want a way to show me the time (in seconds or hours) of a full 24 hour day, where non of these time frames happend. So if a day has 1440 minutes and i only have two time frames (10:30 am to 10:45 am and 10:40 am to 10:45 am) i want the result to be 1425 minutes (and not 1420).
I hope this makes sense.
Thanks
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.
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.
Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
