Forum Discussion

bbrauer100's avatar
bbrauer100
New Member
10 years ago

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's avatar
Anonymous
Not applicable
10 years ago

 

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?

 

ProjectIDTaskDescriptionStart dateStart timeEnd dateEnd timeDurationTags
018/CADLibrary Management26/08/201415:48:2926/08/201416:45:5600:57:27Cont. Impr. - Templates / Families
018/CADRevit Training Sessions preparation11/09/201409:09:2511/09/201411:34:3102:25:06Cont. Impr. - Training / Induction
018/CADCPD Feedback form fill out11/09/201417:11:5711/09/201417:29:0600:17:09Cont. Impr. - Training / Induction
018/CADRailings CPD Preparation12/09/201416:00:0812/09/201417:23:2001:23:12Cont. Impr. - Training / Induction
018/CADTraining area setup15/09/201417:00:3315/09/201417:31:4800:31:15Cont. Impr. - Training / Induction
018/CADRailings CPD Preparation18/09/201413:45:5118/09/201417:32:0803:46:17Cont. Impr. - Training / Induction
018/CADCT Template25/09/201408:56:4625/09/201410:08:5301:12:07Cont. Impr. - Standards / Guides
018/CADBIM Conference report13/10/201412:55:1213/10/201413:06:3300:11:21Cont. Impr. - Conferences & Workshops
018/CADRevit training sessions outline and preparation27/10/201415:20:5027/10/201415:27:5400:07:04Cont. Impr. - Training / Induction
018/CADCPD on Views preparation13/04/201513:25:1413/04/201515:26:3602:01:22Cont. Impr. - Training / Induction
018/CADTask review and Management29/04/201517:29:4029/04/201517:30:3800:00:58Office procedures
018/CADHTG Packaging and uploading to the Hub01/05/201511:07:4101/05/201512:03:3000:55:49Cont. Impr. - Standards / Guides
018/CADBEP Template update12/05/201511:25:3512/05/201511:32:1700:06:42Cont. Impr. - Templates / Families
018/CADTask review and Management10/07/201513:43:2610/07/201513:44:0000:00:34Office
018/CADTask review and Management14/07/201509:38:3914/07/201509:43:1600:04:37Office
018/CADCPD Curtain Walls17/07/201511:51:3517/07/201512:31:5600:40:21Cont. Impr. - Training / Induction
018/CADCPD - The Series07/04/201610:51:0507/04/201610:59:4500:08:40Cont. Impr. - Training / Induction
018/CADAudits Dashboard08/04/201616:41:1308/04/201617:39:1200:57:59Cont. Impr. - R&D
018/CADAudits Dashboard08/04/201616:09:3208/04/201616:17:2400:07:52Cont. Impr. - R&D
wonga's avatar
wonga
Continued Contributor
10 years ago

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.

 

  • wonga's avatar
    wonga
    Continued Contributor
    10 years ago

    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's avatar
    Anonymous
    Not applicable
    10 years ago

    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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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