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....
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 |
Hey Anonymous,
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.
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.
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 Anonymous 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.pbix
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.
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
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