Forum Discussion
Time to Duration conversion throws an error.
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....
BAWardbbrauer100Anonymous 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.
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?
Anonymous,
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.
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 Anonymous 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.