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

[Anonymous]

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)

[v-frfei-msft]

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]

Hi v-frfei-msft,

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 · Accepted solution]

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

[Anonymous]

Hi Jimmy,

I woulk like to thank you for your solution, it's magic :), as I am a newbie with Power Query (1 month experience)  so it took me like two days to be able to adjust your solution to my need (table and datetime start and so on. 🙂 ). much appreciated thanks again.

[Jimmy801]

Hello @Anonymous 

great that the time spent for your solution was good for something

have a nice day

Jimmy

[Anonymous]

Hi Jimmy,

I have just realized that my liste of intervals changes according to the StartTime=StatusDateTime in my Called table.

so I have to change this part of code every day to adjust it to get the interval like: 12:00:00   to 12:30:00 and so on... instead of( 12:35:03 to 01:05:03).

from my side I did some change to this part of code for today to get the right intervals:

= List.DateTimes
    (StartDate - #duration(0,0,Time.Minute( List.Min(ChangeType[StatusDateTime])- #duration(0,0,-35,0)),0),
    Duration.Minutes(List.Max(ChangeType[StatusDateTime]) - List.Min(ChangeType[StatusDateTime])) / 3+30, #duration(0,0,30,0) )

Is there any way to have my liste of Intervals fixed as shown below and independent of the start time of my called table.

thanks in advance.

[Jimmy801]

Hello
If i remember right the list is created dynamically depending on the datetimes found in the database and starting always at full hour.
So i don't understand the issue. You could apply a time filter on top to visualise only today's data, or I'm missing something?
Jimmy

[Syndicate_Admin]

Hi everyone,

I am having some difficulties figuring out how to shape my table the way I would like to with power Query.

I would like to keep only the duplicated  numbers in RemoteID Column when MediaType Column contains value =6  and then compute the difference in time between the first line and 2nd line as showed in picture below.

Thank you in advance

[Anonymous]

Hi everyone,

I am having some difficulties figuring out how to shape my table the way I would like to with power Query.

I would like to keep only the duplicated  numbers in RemoteID Column when MediaType Column contains value =6  and then compute the difference in time between the first line and 2nd line as showed in picture below.

Thank you in advance

[Syndicate_Admin]

Hello @Gmayha 

could you please create a new post for this issue

Thanks

Jimmy

[Jimmy801]

Hello @Anonymous 

could you please create a new post for this issue

Thanks

Jimmy

[Anonymous]

Hi Jimmy,

yes it is a different issue should be a new post. thanks for the note. I did a new post.

thanks in advance.