How to find Date ranges NOT intersecting

[Anonymous]

Hello Everyone I am looking for a little help with this one if you don't mind. 

I have start and end datetimes for a server being online. I want to return a table for the start and end datetimes the server was not online (over a large datetime range). I have a single table with all date ranges and status in. 

Here is the file https://1drv.ms/x/s!AhxwTE9UMcGrhHaTgy3_M-ZoXBXY?e=kxavAg

Any pointers would be really appreciated. 

Many thanks in advance

Will

[Icey]

Hi @Anonymous ,

Is this problem solved?

Best Regards,

Icey

[Greg_Deckler]

@Anonymous I haven't had a chance to post this to the Quick Measures Gallery yet but seems like you want this but in reverse, should be fairly easy to modify. This is for finding the total amount of time between overlapping appointments. 

Overlap

Overlap = 
    VAR __Start = MIN('Table'[Start])
    VAR __End = MAX('Table'[End])
RETURN
    IF(ISBLANK(MAX([Start])),BLANK(),
        VAR __Table = GENERATESERIES(__Start,__End,1/24/60)
        VAR __Table1 = ALL('Table')
        VAR __Table2 = GENERATE(__Table,__Table1)
        VAR __Table3 = ADDCOLUMNS(__Table2,"Include",IF([Value]>=[Start] && [Value] <= [End],1,0))
        VAR __Table4 = GROUPBY(__Table3,[Value],"Minute",MAXX(CURRENTGROUP(),[Include]))
    RETURN
        SUMX(__Table4,[Minute])/60)

I'm thinking:

Not Included:

Not Included = 
    VAR __Start = MIN('Table'[Start])
    VAR __End = MAX('Table'[End])
RETURN
    IF(ISBLANK(MAX([Start])),BLANK(),
        VAR __Table = GENERATESERIES(__Start,__End,1/24/60)
        VAR __Table1 = ALL('Table')
        VAR __Table2 = GENERATE(__Table,__Table1)
        VAR __Table3 = ADDCOLUMNS(__Table2,"Include",IF(COUNTROWS(FILTER(__Table1,[Value]>=[Start] && [Value] <= [End])>=1)),0,1))
        VAR __Table4 = GROUPBY(__Table3,[Value],"Minute",MAXX(CURRENTGROUP(),[Include]))
    RETURN
        SUMX(__Table4,[Minute])/60)

Let me know if that works and I'll include it when I post to the quick measures gallery. PBIX is attached below sig (has the Overlap version in it)

[Anonymous]

Many thanks @Greg_Deckler, that is super useful but not exactly what i was looking for. I don't think i was as clear as i should have been. 

The output i want is a table, start, end datetimes with an 'offline/online' flag status. So creating an 'anti-table' and then appending to the initial 'online' set of start and end datetimes. 

Many thanks

Will

[CNENFRNL · Accepted solution]

Hi, @Anonymous , to my understanding, you'd like a table cleansed by PQ. Pls refer to this,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZdNbhsxDIWvMvC2scE/aShdJciyAQoU6f135Ywsx+3QGjHwjsQXkuKT3ry/XxYCgivkK9GCVBNU0B+AFWC5vP0TJagsNeVbUbC/lmQ5f75+//r6efl4e4bJAlqZX8DSAlBTqWmdgln6Fq6QPVjuUbppPoflM5gVzRV5qrJ1QalMVdw2tbc5NzPdK8NKL2DElaZhZcFU7UCZPVhZiOZhDAuuWzqDA2tRgYrlHLZeAbfVSFIJDzCLUmBmLX2fGYsH4xiMO2z1YBJY2pbeYMc9s+iuAJmurCsA3ZnlWGV5WNkam9naYcmDRRTQ0geVlRisjGAIoZlt6a/bRIzBsAvd2zOkGIzuq5E8BVjUroF5GG9vgKC/Z1tU9ltjDibbjWX/G4sHS6HTxDQSOuaFrGi7ueUm5RzWFGCdurA1dgBrh7kz0xisKSD7lyPGFIBdAeIdAEEIRg8FuLCYAgjvM3P3jGIKoKYA8a9t4hiMR0Inic1MhrCYAiiN7jOKvQGUh5XF3gBah5XF3oC7C7LVcE+zxNosdxh4bXJMAQy9sqMLsijGYP0NcJyj7h6ptylwAmvprc2j2dOYpdInS3WcmcYslT5ZKhcWkZN2S2VtHg9A75ZqHjaQkz5ZqjlYf1DQPYDIg6LdUklFt7KHnGQK9rBURzlpzFJpt1SuAjRmqbRbKns3vTbNI9n3Zsr2/XLLLKcwHJ1myFJpt1QvlhZjCkDuM3NhMQVgU4B92B2NS4vaN9rsZ/V+x+w/h8Xf0Rv8dwN9fjbYx18=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [start = _t, end = _t, status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"start", type datetimezone}, {"end", type datetimezone}, {"status", type text}}),

    //Sorting table by "start" for possible time overlap
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"start", Order.Ascending}}),
    tb = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(tb, "Custom", each 
        [
            //in case of time overlap, Start of "offline" period is null
            Start = if tb[end]{[Index]} < tb[start]{[Index] + 1} then tb[end]{[Index]} else null,
            End = if Start is null then null else tb[start]{[Index]+1},
            Status = "Offline"
        ]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"start", "end", "status", "Index"}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"Start", "End", "Status"}, {"Start", "End", "Status"}),

    //filter out null and error at your own will
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each try [Start] <> null otherwise false)
in
    #"Expanded Custom"

As there are time overlaps in the dataset, time overlap examine should be conducted.

[Anonymous]

link not working properly, try this. https://1drv.ms/u/s!AhxwTE9UMcGrhHjf-GwydzTcPw5L?e=A84SzD

Thanks Will