Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to find Date ranges NOT intersecting

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

 

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    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.

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        CNENFRNL
        Community Champion
        5 years ago

        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.

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Is this problem solved?

     

     

    Best Regards,

    Icey