Duration Data for call center

[pete.langlois]

I’m working with call center data and would like to create a column that describes how many seconds each caller is stuck in the queue while waiting to be connected with an agent. The data is extracted as a flat excel file, in the following format:

When I pull this datafile into power query, this column is automatically converted into a time format, and I can’t figure out how to covert it into duration. When I do select data type = duration, it returns an error.

Ideally, I’d like for this inque time column to be converted to total seconds.

IE

00:01:50 would translate to 110 seconds (one minute and 50 seconds).

I attached some sample data in case this is helpful for loading into PBI and testing.

Thanks for your help!

Pete
inque sample data.xlsx (11.0 KB)

[Harsh]

Hello @pete.langlois,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve specified. Below are the steps listed as follows -

1). When data is imported in Power Query. By default, it’ll convert the “Inqueue Time” column into a “Time” data type. Below is the screenshot of the code generated by default inside Power Query upon loading the data (See the highlighted red box in the image) -

2). Convert that into a text. That is, replace “type time” with “type text”. See this -

Upon changing the format to text, this is how the “Inqueue Time” column will look like -

3). Go to the “Transform” tab and add a Prefix to this column i.e., “0.”.

4). Now, convert the data type of this column into the “Duarion” type.

5). Finally, from the “Transform” tab, go to the “Duration” section and select the option of “Total Seconds”. See this -

And now, you’ll see the results of “Inqueue Time” column in terms of “Total Seconds”.

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Converting Inqueue Time Into Total Seconds - Harsh.pbix (41.7 KB)

[pete.langlois]

Got it, thanks Harsh!