Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

spread calls duration that exceed 30 mins over multiple 30 mins intervals over the day.

Hi team,

I would like your help on this:

I am working on a table that has multiple columns:  User, StartTime , EndTime , AuxCode and Duration columns.

I would like to compute the number of AuxCode(duration ) used for every 30 mins Interval based on the Column StartTime and EndTime like follow: EndTime - tartTime = Duration (seconds).

the issue here is when we do EndTime - tartTime = Duration (seconds)

 

 

 

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello Anonymous 

     

    I've created something really nice

    But I think the logic has to be changed, meaning, creating a talble, considering the lowest start time and the highest end time and then connect to the call-table to do the calculation

    let

    
    
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTWMzTSMzIwtFQwsLAyMFDSQRMyNgUKJSfm5CjF6qArNzFFU26JrDwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t, Type = _t]),
    ChangeType = Table.TransformColumnTypes
        (
            Quelle,
            {
                {"Start", type datetime}, 
                {"End", type datetime}, 
                {"Type", type text}
            }
        ),
        GetListTimesStart = List.DateTimes
    (
        List.Min
        (
            ChangeType[Start]
        )
        -
        #duration(0,0,Time.Minute
        (
            List.Min
            (
                ChangeType[Start]
            )
        ),0),
        Duration.Minutes
        (
            List.Max
            (
                ChangeType[End]
            )
            -
            List.Min
            (
                ChangeType[Start]
            )

        )
        / 30+2,
        #duration(0,0,30,0)
    ),
    GetListTimeEnd = List.Transform(GetListTimesStart, each _ + #duration(0,0,30,0)),
    CreateTabel = Table.FromColumns({GetListTimesStart,GetListTimeEnd}, {"Start", "End"}),
    AddCallTable = Table.AddColumn
    (
        CreateTabel, 
        "Merge Tables", 
        (Time)=> Table.SelectRows
        (
            ChangeType, 
            (select)=> select[Start]<=Time[End] and Time[Start] <= select[End] 
        )
    ),
    ExpandCallTimes = Table.ExpandTableColumn
    (
        AddCallTable, 
        "Merge Tables", 
        {"Start", "End", "Type"}, 
        {"Call.Start", "Call.End", "Type"}
    ),
    ChangeToDateTime = Table.TransformColumnTypes
    (
        ExpandCallTimes,
        {
            {"Start", type datetime}, 
            {"End", type datetime}, 
            {"Call.Start", type datetime}, 
            {"Call.End", type datetime}}
    ),
    CalculateDuration = Table.AddColumn
    (
        ChangeToDateTime, 
        "Duration", 
        each Duration.TotalSeconds
        (
            (if [End]<[Call.End] then [End] else [Call.End])-
            (if [Call.Start]>[Start] then [Call.Start] else [Start])
        )
    ),
    DeleteCallColumns = Table.RemoveColumns
    (
        CalculateDuration,
        {"Call.Start", "Call.End"}
    )
in
    DeleteCallColumns

     

    What do you think about it?

    If this post helps or solves your problem, please mark it as solution.
    Kudos are nice to - thanks
    Have fun

    Jimmy

12 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi ,

      Thank you for your help.

       

      I got some issues here when the call Duration Exceeds 30mins = 30*60 = 1800 sec. (e.g: if a call lasts for 1h so 1h=60*60=3600 secs only for the first interval because the calls are initially saved to table by occurrence and not by interval so it count only for the first Interval even if the call exceed 30 mins-Interval.

      Let say we have a call that last for 1900 secs that is recorded for the first interval 8:00:00 to 8:30:00.  so I would like to have a new row created for the extra portion of this call that exceed 1900 -1800 = 100 secs and be recorded in the next interval 8:30:00 to 9:00:00 .

      so I would like to find a way to distribute my calls duration that exceed 30mins over all intervals accordingly using power Quey.

       

      example:

      original table:

      startTime   Endtime   Auxcode      Duration(seconds)

      8:00:00       8:31:40     acd call       1900                (00:31:40)

      8:31:40       9:01:39     training       1800               (00:30:00)

       

      would like to have (round down to 30mins interval and take only 1800 secs for each status call when exceed 30mins duration as below :

       

      8:00:00       8:30:00     acd call       1800

      8:30:00       9:00:00     acd call       100

      8:30:00       9:00:00    training       1700

      9:00:00       9:30:00    traning        100

      and so on...

      here is a sample of my table.

      thank you in advance.

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        6 years ago

        Hello Anonymous 

         

        I've created something really nice

        But I think the logic has to be changed, meaning, creating a talble, considering the lowest start time and the highest end time and then connect to the call-table to do the calculation

        let

    
    
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTWMzTSMzIwtFQwsLAyMFDSQRMyNgUKJSfm5CjF6qArNzFFU26JrDwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t, Type = _t]),
    ChangeType = Table.TransformColumnTypes
        (
            Quelle,
            {
                {"Start", type datetime}, 
                {"End", type datetime}, 
                {"Type", type text}
            }
        ),
        GetListTimesStart = List.DateTimes
    (
        List.Min
        (
            ChangeType[Start]
        )
        -
        #duration(0,0,Time.Minute
        (
            List.Min
            (
                ChangeType[Start]
            )
        ),0),
        Duration.Minutes
        (
            List.Max
            (
                ChangeType[End]
            )
            -
            List.Min
            (
                ChangeType[Start]
            )

        )
        / 30+2,
        #duration(0,0,30,0)
    ),
    GetListTimeEnd = List.Transform(GetListTimesStart, each _ + #duration(0,0,30,0)),
    CreateTabel = Table.FromColumns({GetListTimesStart,GetListTimeEnd}, {"Start", "End"}),
    AddCallTable = Table.AddColumn
    (
        CreateTabel, 
        "Merge Tables", 
        (Time)=> Table.SelectRows
        (
            ChangeType, 
            (select)=> select[Start]<=Time[End] and Time[Start] <= select[End] 
        )
    ),
    ExpandCallTimes = Table.ExpandTableColumn
    (
        AddCallTable, 
        "Merge Tables", 
        {"Start", "End", "Type"}, 
        {"Call.Start", "Call.End", "Type"}
    ),
    ChangeToDateTime = Table.TransformColumnTypes
    (
        ExpandCallTimes,
        {
            {"Start", type datetime}, 
            {"End", type datetime}, 
            {"Call.Start", type datetime}, 
            {"Call.End", type datetime}}
    ),
    CalculateDuration = Table.AddColumn
    (
        ChangeToDateTime, 
        "Duration", 
        each Duration.TotalSeconds
        (
            (if [End]<[Call.End] then [End] else [Call.End])-
            (if [Call.Start]>[Start] then [Call.Start] else [Start])
        )
    ),
    DeleteCallColumns = Table.RemoveColumns
    (
        CalculateDuration,
        {"Call.Start", "Call.End"}
    )
in
    DeleteCallColumns

         

        What do you think about it?

        If this post helps or solves your problem, please mark it as solution.
        Kudos are nice to - thanks
        Have fun

        Jimmy