Time to Duration conversion throws an error.

[bbrauer100]

I'm trying to convert time of day to duration in order to perform summary calculations.  However, when I try to convert time to duration in Query Editor the following error occurs:

DataFormat.Error: We couldn't convert to Duration.
Details:
    1:11:55 AM

Seems pretty straight forward, so what am I missing?

[BAWard]

I have the exact same problem.  The column of input is formatted as date/time, and represents durations in hours:minutes. 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Me\Documents\Test_Duration_Format.xlsx"), null, true),
    TestColumn_Table = Source{[Item="TestColumn",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(TestColumn_Table,{{"TestDuration", type duration}})
in
    #"Changed Type"

with the following input:

[BAWard]

Forum didn't accept screen shot in my original reply, and would not let me edit it after it  was  submitted, so here is the sample input.  Hopefully it woks this time.

[jagostinhoCT]

I have the exact same problem.

Did you find anything that worked?

Thank you

J

[wonga]

@BAWard@bbrauer100@jagostinhoCT You need to ensure the format of the cells is "Text" first. This can be done either in Excel by highlighting the cells -> Format Cells -> Text, or in Power BI Query Editor by highlighting the column and going to the ribbon -> Transform -> Data Type -> Text. Then you can add a "calculated column" in Power BI Query Editor using the following:

CustomColumn = Duration.FromText([Duration])

You can then extract your desired duration from that new custom column such as: total minutes, total hours, total seconds, etc.

In my experience, I've found the easiest way to use duration is by subtracting an end time from a start time and then converting it to the desired duration. Hope this helps, thanks.

[polymathy2017]

Converted to text a few times in the PQ editor. Didn't work in any scenario. Tried each of the following and all results in Duration errors:

1. Duplicated values column, changed format to Text
2. Added a column with MCode using Duration.From
3. Added a column with MCode using Duration.FromText

all produced the same.

[jagostinhoCT]

But if it is converted to text how can it then be used on graphs?

Say, for instance, where one wants to graph total amount of time spent on a project?

[wonga]

@jagostinhoCT, 

Well in that case, you would have start times and end times right? The scenario that was presented here was just a table of durations.

Ideally, if you were tracking the progress of a project you would start off with a start date/time and end date/time. These would be considered as DateTime values in Power BI.

From the query editor, you would be able to calculate the duration of the project by doing [End Time] - [Start Time] and then converting that value into the duration you want. Once you get all that data sorted out, then you would use the appropriate visual to display those values.

Possible visuals would be Gannt or horizontal bar chart.

If you can provide me with a sample dataset, I'll see what I can mock up to give you a better idea of how to do it.

[jagostinhoCT]

@wonga

See a sample of the date I want to use below.

Thank you

ProjectID	TaskDescription	Start date	Start time	End date	End time	Duration	Tags
018/CAD	Audits Dashboard	08/04/2016	16:41:13	08/04/2016	17:39:12	00:57:59	Cont. Impr. - R&D
018/CAD	Railings CPD Preparation	12/09/2014	16:00:08	12/09/2014	17:23:20	01:23:12	Cont. Impr. - Training / Induction
018/CAD	Task review and Management	10/07/2015	13:43:26	10/07/2015	13:44:00	00:00:34	Office
018/CAD	CPD - The Series	07/04/2016	10:51:05	07/04/2016	10:59:45	00:08:40	Cont. Impr. - Training / Induction
018/CAD	Revit Training Sessions preparation	11/09/2014	09:09:25	11/09/2014	11:34:31	02:25:06	Cont. Impr. - Training / Induction
018/CAD	CPD on Views preparation	13/04/2015	13:25:14	13/04/2015	15:26:36	02:01:22	Cont. Impr. - Training / Induction
018/CAD	Revit training sessions outline and preparation	27/10/2014	15:20:50	27/10/2014	15:27:54	00:07:04	Cont. Impr. - Training / Induction
018/CAD	Task review and Management	14/07/2015	09:38:39	14/07/2015	09:43:16	00:04:37	Office
018/CAD	CT Template	25/09/2014	08:56:46	25/09/2014	10:08:53	01:12:07	Cont. Impr. - Standards / Guides
018/CAD	Task review and Management	29/04/2015	17:29:40	29/04/2015	17:30:38	00:00:58	Office procedures
018/CAD	Audits Dashboard	08/04/2016	16:09:32	08/04/2016	16:17:24	00:07:52	Cont. Impr. - R&D
018/CAD	Library Management	26/08/2014	15:48:29	26/08/2014	16:45:56	00:57:27	Cont. Impr. - Templates / Families
018/CAD	BIM Conference report	13/10/2014	12:55:12	13/10/2014	13:06:33	00:11:21	Cont. Impr. - Conferences & Workshops
018/CAD	CPD Feedback form fill out	11/09/2014	17:11:57	11/09/2014	17:29:06	00:17:09	Cont. Impr. - Training / Induction
018/CAD	BEP Template update	12/05/2015	11:25:35	12/05/2015	11:32:17	00:06:42	Cont. Impr. - Templates / Families
018/CAD	Training area setup	15/09/2014	17:00:33	15/09/2014	17:31:48	00:31:15	Cont. Impr. - Training / Induction
018/CAD	HTG Packaging and uploading to the Hub	01/05/2015	11:07:41	01/05/2015	12:03:30	00:55:49	Cont. Impr. - Standards / Guides
018/CAD	Railings CPD Preparation	18/09/2014	13:45:51	18/09/2014	17:32:08	03:46:17	Cont. Impr. - Training / Induction
018/CAD	CPD Curtain Walls	17/07/2015	11:51:35	17/07/2015	12:31:56	00:40:21	Cont. Impr. - Training / Induction
018/CAD	staff induction	02/02/2015	15:16:37	02/02/2015	15:22:12	00:05:35	Cont. Impr. - Training / Induction
018/CAD	CT Template update	25/06/2015	14:00:33	25/06/2015	16:12:58	02:12:25	Cont. Impr. - Templates / Families
018/CAD	CT Template CPD setup	13/02/2015	15:53:30	13/02/2015	16:13:57	00:20:27	Cont. Impr. - Training / Induction
018/CAD	Project Workflow Checklist	02/06/2015	16:02:14	02/06/2015	16:06:37	00:04:23	Cont. Impr. - Standards / Guides
018/CAD	Revit training sessions outline and preparation	20/11/2014	16:51:00	20/11/2014	17:15:00	00:24:00	Cont. Impr. - Training / Induction
018/CAD	BIM Meeting with David	25/02/2015	16:53:20	25/02/2015	17:06:29	00:13:09	BIM Management Meeting
018/CAD	BIM Glue and Collaboration on Simon Blakey's pc	05/04/2016	09:05:33	05/04/2016	12:04:31	02:58:58	Cont. Impr. - IT
018/CAD	Library Management	26/08/2014	14:38:56	26/08/2014	14:48:51	00:09:55	Cont. Impr. - Templates / Families
018/CAD	View Templates transfer problem report to Autodesk	06/10/2014	11:41:20	06/10/2014	13:13:05	01:31:45	BIM Project Support
018/CAD	Revit Training Sessions preparation	10/09/2014	14:07:07	10/09/2014	14:18:46	00:11:39	Cont. Impr. - Training / Induction
018/CAD	Skills Self Assessment update	09/12/2014	14:32:07	09/12/2014	14:36:37	00:04:30	Cont. Impr. - Training / Induction
018/CAD	Issue management software search	11/09/2014	14:27:55	11/09/2014	16:04:30	01:36:35	Cont. Impr. - R&D
018/CAD	Update Running Projects to current Template Version	23/04/2015	16:05:08	23/04/2015	16:34:24	00:29:16	Reviews / Surveys / Assessments
018/CAD	Revit titleblocks for Moscow	12/09/2014	12:02:22	12/09/2014	13:02:35	01:00:13	Cont. Impr. - Templates / Families
018/CAD	BDSU2015Ult LON Deployment	09/06/2015	13:53:49	09/06/2015	13:57:41	00:03:52	Cont. Impr. - IT
018/CAD	View Templates	28/08/2014	11:56:41	28/08/2014	12:51:38	00:54:57	Cont. Impr. - Standards / Guides
018/CAD	CT Template update	20/03/2015	09:44:39	20/03/2015	09:51:38	00:06:59	Cont. Impr. - Standards / Guides
018/CAD	CT Template setup	24/10/2014	10:57:53	24/10/2014	13:38:30	02:40:37	Cont. Impr. - Standards / Guides
018/CAD	BDSU2015 MAN Deployment update	01/05/2015	15:51:20	01/05/2015	17:06:09	01:14:49	Cont. Impr. - IT
018/CAD	Revit training sessions outline and preparation	29/10/2014	08:45:00	29/10/2014	09:46:00	01:01:00	Cont. Impr. - Training / Induction
018/CAD	Marketing CT BIM Image	20/01/2015	13:01:11	20/01/2015	13:01:12	00:00:01	Cont. Impr. - Marketing
018/CAD	Render templates	01/09/2014	14:40:52	01/09/2014	15:06:47	00:25:55	Cont. Impr. - Standards / Guides
018/CAD	new starters followup	20/07/2015	10:58:37	20/07/2015	11:03:49	00:05:12	Cont. Impr. - Training / Induction
018/CAD	RAM install for A630-BPT pcs	07/11/2014	10:02:29	07/11/2014	10:32:36	00:30:07	Cont. Impr. - IT
018/CAD	BIM Review Template	14/05/2015	10:02:03	14/05/2015	10:09:14	00:07:11	Cont. Impr. - Templates / Families
018/CAD	CT Template	24/09/2014	10:01:41	24/09/2014	13:06:55	03:05:14	Cont. Impr. - Standards / Guides
018/CAD	CT Template update	25/06/2015	16:19:02	25/06/2015	16:31:02	00:12:00	Cont. Impr. - Templates / Families
018/CAD	CT Template	25/09/2014	13:34:38	25/09/2014	15:32:17	01:57:39	Cont. Impr. - Standards / Guides
018/CAD	Self Assessment Record update	06/03/2015	16:07:09	06/03/2015	16:16:59	00:09:50	Cont. Impr. - Training / Induction
018/CAD	Revit training sessions outline and preparation	19/11/2014	09:52:42	19/11/2014	10:16:10	00:23:28	Cont. Impr. - Training / Induction
018/CAD	Task review and Management	30/06/2015	15:55:55	30/06/2015	16:05:09	00:09:14	Office
018/CAD	CT Titleblocks	29/09/2014	16:25:34	29/09/2014	17:14:28	00:48:54	Cont. Impr. - Templates / Families
018/CAD	Loretta Collins BIM CAD Induction	23/02/2015	12:36:52	23/02/2015	13:06:20	00:29:28	Cont. Impr. - Training / Induction
018/CAD	Revit Training Sessions preparation	04/09/2014	14:25:55	04/09/2014	17:30:37	03:04:42	Cont. Impr. - Training / Induction
018/CAD	BEP Template update	14/04/2015	09:21:33	14/04/2015	09:30:57	00:09:24	Cont. Impr. - Standards / Guides
018/CAD	Railings CPD Preparation	01/10/2014	13:58:35	01/10/2014	14:47:15	00:48:40	Cont. Impr. - Training / Induction
018/CAD	Audits Dashboard	08/04/2016	15:11:26	08/04/2016	15:35:45	00:24:19	Cont. Impr. - R&D
018/CAD	Library Management	26/08/2014	12:02:09	26/08/2014	12:06:50	00:04:41	Cont. Impr. - Templates / Families
018/CAD	Reading ct documentation	11/08/2014	09:00:00	11/08/2014	17:30:00	08:30:00	Cont. Impr. - Training / Induction
018/CAD	Revit training sessions outline and preparation	01/12/2014	11:58:39	01/12/2014	12:24:49	00:26:10	Cont. Impr. - Training / Induction
018/CAD	Revit software tweak or cleanup	03/10/2014	17:22:21	03/10/2014	17:27:13	00:04:52	Cont. Impr. - IT
018/CAD	Smart Browser Setup	23/04/2015	15:21:42	23/04/2015	15:40:25	00:18:43	Cont. Impr. - R&D
018/CAD	Library Management	26/08/2014	17:31:24	26/08/2014	17:48:14	00:16:50	Cont. Impr. - Templates / Families
018/CAD	Model Review tool	29/04/2015	17:30:46	29/04/2015	17:47:42	00:16:56	Cont. Impr. - R&D
018/CAD	BIM International Conference, Lisbon	09/10/2014	09:00:00	09/10/2014	18:00:00	09:00:00	Cont. Impr. - Conferences & Workshops
018/CAD	Task review and Management	22/05/2015	09:30:56	22/05/2015	09:45:01	00:14:05	Office
018/CAD	CT Template setup	14/10/2014	10:32:31	14/10/2014	10:32:38	00:00:07	Cont. Impr. - Standards / Guides
018/CAD	Hotdesk setup	11/12/2014	12:51:00	11/12/2014	13:15:00	00:24:00	Cont. Impr. - IT
018/CAD	CT Template setup	15/10/2014	13:11:30	15/10/2014	13:18:32	00:07:02	Cont. Impr. - Standards / Guides
018/CAD	BDSU2015MAN02 Deployment ACAD update	11/05/2015	11:44:44	11/05/2015	11:57:26	00:12:42	Cont. Impr. - IT
018/CAD	Revit training sessions outline and preparation	15/12/2014	15:31:38	15/12/2014	18:02:28	02:30:50	Cont. Impr. - Training / Induction
018/CAD	Revit training sessions outline and preparation	16/12/2014	16:07:00	16/12/2014	18:36:35	02:29:35	Cont. Impr. - Training / Induction
018/CAD	BPG Formulas for everyday usage	12/09/2014	14:23:25	12/09/2014	15:49:43	01:26:18	Cont. Impr. - Standards / Guides
018/CAD	BIM Management meeting with David Wood	10/06/2015	09:30:37	10/06/2015	10:25:58	00:55:21	BIM Management Meeting
018/CAD	Revit training sessions outline	06/01/2015	10:37:32	06/01/2015	12:06:50	01:29:18	Cont. Impr. - Training / Induction
018/CAD	2015 Deployment	18/03/2015	09:34:57	18/03/2015	10:01:44	00:26:47	Cont. Impr. - IT
018/CAD	CT Template setup	22/10/2014	10:22:12	22/10/2014	13:06:40	02:44:28	Cont. Impr. - Standards / Guides
018/CAD	BEP Template update	12/05/2015	15:00:15	12/05/2015	15:14:04	00:13:49	Cont. Impr. - Templates / Families
018/CAD	Drawing Graphic Comparison for View Template Setting	28/08/2014	16:09:23	28/08/2014	17:53:20	01:43:57	Cont. Impr. - Standards / Guides
018/CAD	BIM Manager's meeting preparation	03/03/2015	13:46:33	03/03/2015	14:49:27	01:02:54	BIM Management Meeting
018/CAD	Revit skills self assessmente - training class update	14/01/2015	15:10:56	14/01/2015	15:43:44	00:32:48	Cont. Impr. - Training / Induction
018/CAD	Reviewing Projects BIM Reviews status	12/06/2015	14:47:12	12/06/2015	14:58:20	00:11:08	BIM Management
018/CAD	Drawing Graphic Comparison for View Template Setting	29/08/2014	17:07:43	29/08/2014	17:19:12	00:11:29	Cont. Impr. - Standards / Guides
018/CAD	BIM Review Template	27/04/2015	12:26:32	27/04/2015	12:37:53	00:11:21	Cont. Impr. - Templates / Families
018/CAD	Revit training sessions outline and preparation	29/10/2014	13:36:00	29/10/2014	16:09:36	02:33:36	Cont. Impr. - Training / Induction
018/CAD	HTG Model Validation	08/05/2015	09:48:04	08/05/2015	10:05:36	00:17:32	Cont. Impr. - Standards / Guides
018/CAD	Revit Skills Self Assessment Update	25/03/2015	10:34:00	25/03/2015	11:34:00	01:00:00	Reviews / Surveys / Assessments
018/CAD	CPD on Views preparation	13/04/2015	12:22:51	13/04/2015	12:55:28	00:32:37	Cont. Impr. - Training / Induction
018/CAD	CT Template	22/09/2014	10:19:11	22/09/2014	11:51:08	01:31:57	Cont. Impr. - Standards / Guides
018/CAD	Task review and Management	02/06/2015	15:52:11	02/06/2015	15:54:31	00:02:20	Office
018/CAD	Model Audit Template revision	06/11/2014	17:00:23	06/11/2014	17:35:20	00:34:57	Reviews / Surveys / Assessments
018/CAD	Kiwi Bonus tools price	29/01/2015	12:04:38	29/01/2015	12:25:21	00:20:43	Cont. Impr. - R&D
018/CAD	Knowledge Smart test	29/01/2015	16:54:34	29/01/2015	18:01:24	01:06:50	Cont. Impr. - Training / Induction
018/CAD	Bristol BDSU2014 Deployment	18/05/2015	17:49:56	18/05/2015	18:11:22	00:21:26	Cont. Impr. - IT
018/CAD	CT Template	24/09/2014	09:35:20	24/09/2014	09:52:51	00:17:31	Cont. Impr. - Standards / Guides

[wonga]

@jagostinhoCT Can you provide a dataset sample with proper formatting? For example your start date and end dates are not consistent in formatting. Some of them are formatted correctly in mm/dd/yyyy whereas others are in a weird format like dd/mm/yyyy.

[jagostinhoCT]

@wonga

I was unable to find how to attach an Excel file.

The dates are all formatted as dd/mm/yyyy. 

I have sorted them by Start Date.

Does the one below work?

ProjectID	TaskDescription	Start date	Start time	End date	End time	Duration	Tags
018/CAD	Library Management	26/08/2014	15:48:29	26/08/2014	16:45:56	00:57:27	Cont. Impr. - Templates / Families
018/CAD	Revit Training Sessions preparation	11/09/2014	09:09:25	11/09/2014	11:34:31	02:25:06	Cont. Impr. - Training / Induction
018/CAD	CPD Feedback form fill out	11/09/2014	17:11:57	11/09/2014	17:29:06	00:17:09	Cont. Impr. - Training / Induction
018/CAD	Railings CPD Preparation	12/09/2014	16:00:08	12/09/2014	17:23:20	01:23:12	Cont. Impr. - Training / Induction
018/CAD	Training area setup	15/09/2014	17:00:33	15/09/2014	17:31:48	00:31:15	Cont. Impr. - Training / Induction
018/CAD	Railings CPD Preparation	18/09/2014	13:45:51	18/09/2014	17:32:08	03:46:17	Cont. Impr. - Training / Induction
018/CAD	CT Template	25/09/2014	08:56:46	25/09/2014	10:08:53	01:12:07	Cont. Impr. - Standards / Guides
018/CAD	BIM Conference report	13/10/2014	12:55:12	13/10/2014	13:06:33	00:11:21	Cont. Impr. - Conferences & Workshops
018/CAD	Revit training sessions outline and preparation	27/10/2014	15:20:50	27/10/2014	15:27:54	00:07:04	Cont. Impr. - Training / Induction
018/CAD	CPD on Views preparation	13/04/2015	13:25:14	13/04/2015	15:26:36	02:01:22	Cont. Impr. - Training / Induction
018/CAD	Task review and Management	29/04/2015	17:29:40	29/04/2015	17:30:38	00:00:58	Office procedures
018/CAD	HTG Packaging and uploading to the Hub	01/05/2015	11:07:41	01/05/2015	12:03:30	00:55:49	Cont. Impr. - Standards / Guides
018/CAD	BEP Template update	12/05/2015	11:25:35	12/05/2015	11:32:17	00:06:42	Cont. Impr. - Templates / Families
018/CAD	Task review and Management	10/07/2015	13:43:26	10/07/2015	13:44:00	00:00:34	Office
018/CAD	Task review and Management	14/07/2015	09:38:39	14/07/2015	09:43:16	00:04:37	Office
018/CAD	CPD Curtain Walls	17/07/2015	11:51:35	17/07/2015	12:31:56	00:40:21	Cont. Impr. - Training / Induction
018/CAD	CPD - The Series	07/04/2016	10:51:05	07/04/2016	10:59:45	00:08:40	Cont. Impr. - Training / Induction
018/CAD	Audits Dashboard	08/04/2016	16:41:13	08/04/2016	17:39:12	00:57:59	Cont. Impr. - R&D
018/CAD	Audits Dashboard	08/04/2016	16:09:32	08/04/2016	16:17:24	00:07:52	Cont. Impr. - R&D

[wonga]

@jagostinhoCT I've made a PBIX file but I can't get it to you yet. I need to find a way to upload it, thanks.

EDIT: I was able to upload it to the Power BI Community: http://community.powerbi.com/oxcrx34285/attachments/oxcrx34285/DesignMakeover052016/5/2/sample_ds.pb...

I had to reformat some of the start date and time data in an excel file, but you should be able to figure out how I got the calculated columns. Let me know if this is what you were looking for or at least if I was on the right track, thanks.

[jagostinhoCT]

@wonga

Even though I am not currently looking for a Gantt the new modelling works.

At the moment, I am trying to learn how to get sums of duration for specific tags. As per image below.

An error ensues when trying to edit the queries so I cannot see the steps taken. Tried to copy/paste the data into an excel and replace the source but it breaks as well. Would it be possible to ask for the excel you used? (BTW, how can one include an attachment?)

So, it seems that I may need to have different calculated columns to suffice different reporting needs. I may need a single column where it converts all into minutes and another one was Text for splitting time into mins, hours, days.

Thank you very much for your help

[wonga]

Hey @jagostinhoCT,

See if you can access this Google Drive documents: https://drive.google.com/open?id=0B-bZzUsHzLUnZ3ZUY05PQ1gwVmM

I made an edit to the queries, made a mistake where it's not getting the total minutes or hours and only extracting the minutes or hours part of the overall duration. Look at page 2 of the PBIX file and see if it's what you are looking for. 

Thanks.

[jagostinhoCT]

Hello @wonga

I apologise for the late reply.

The file you sent me seems to be it.

Thank you very much for yout time and help.

Kind regards,

Joaquim

[Greg_Deckler]

Can you post sample data and your formula? Basically impossible to give you any meaningful feedback otherwise.