Time problem

P

Paul_Christie

New member
Joined
Mar 23, 2016
Messages
153
Reaction score
0
Points
0
Location
Nottingham, UK
Website
www.instantpages.ltd.uk
Excel Version(s)
Office 365 Monthly update
I've got some text data that is being imported and mostly falling in to the correct data types. I've got an issue with three fields that are all Time fields. I've got a StartTime and an EndTime which are exactly what they say they are and I can easily setup another field called WorkingTime where I do EndTime-StartTime and get a correct answer. I've also got a field called BreakDuration that comes in as a Time field. When I set a custom field as EndTime-StartTime-BreakDuration I get an error. I've tried changing the BreakDuration field to a Duration type but I'm still getting an error.

I think I'm missing something obvious but I need someone to point me in the right die=restion with this please.

Paul
 
Are you able to attach a sample file showing this problem?
 
I applied this formula in column F and I don't see any errors...

=[@FINISHTIME]-[@STARTTIME]-[@BREAKDURATION]


 
I agree it works fine in Excel. What I'm trying to do is use Power Query to get the data from a bunch of text files. It's the Power Query formula that I can't get to work.

Paul
 
To explain more fully;

This = Table.AddColumn(#"Added Custom5", "WT", each [FINISHTIME]-[STARTTIME]) works

This = Table.AddColumn(#"Added Custom5", "WT", each [FINISHTIME]-[STARTTIME]-[BREAKDURATION]) produces an error

Paul
 
Oh sorry... I did not notice you were looking at Power Query.... I don't have enough experience with Power Query to quickly answer that..
 
Hey Paul,

So the file is pointing to E:\TNT\T900 2016-2017.xlsx which I don't have. Unfortunately that leaves me dead in the water as I can't modify the queries.

Can you paste a small subset into a table in the sample workbook and pull that?

I have a class for the next few hours, but I'll take a look afterwards.

Cheers!
 
Time Problem

Ken,

Yes, I was hitting some sort of limit. I've attached a small part of one of the original text files that is causing the problem.

REgards
Paul
 

Attachments

  • Test Data File for Ken.txt
    10.1 KB · Views: 14
Hey Paul,

100 rows should more than suffice. 50 would probably do it. Doesn't need to be huge, just something that exhibits the issue. :)
 
Hey Paul,

So I did this:

Code: 
let    Source = Csv.Document(File.Contents("C:\Data\Test Data File for Ken.txt"),[Delimiter=";", Columns=61, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Promoted Headers", {{"ActualSystemDate", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"ActualSystemTime", type time}, {"StartTime", type time}, {"FirstStopTime", type time}, {"LastStopTime", type time}, {"BreakDuration", type duration}, {"DepartureTime", type time}, {"ReturnTime", type time}, {"FinishTime", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [FinishTime]-[StartTime]-[BreakDuration])
in
    #"Added Custom"

And it works nicely. Notice the BreakDuration is a duration. The other thing I would check is the data type on the time columns.

I also noticed that you have a blank row at the end of the sample. You may want to filter that out.

Let me know if that sorts it.
 
Should you prefer time format for the result, then you can add #time(0,0,0) as in:

Code: 
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #time(0,0,0)+([FinishTime]-[StartTime]-[BreakDuration]))
 
How did I miss page 2???

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="RoundData"]}[Content],
    ConvertStartTime = Table.AddColumn(Source, "ST", each Number.From([STARTTIME])),
    ConvertEndTime = Table.AddColumn(ConvertStartTime, "ET", each Number.From([FINISHTIME])),
    ExtractBreakDuration = Table.AddColumn(ConvertEndTime, "BT", each Number.Mod(Number.From([BREAKDURATION]), 1)),
    AddWT = Table.AddColumn(ExtractBreakDuration, "WT", each Time.From([ET]-[ST]-[BT])),
    #"Removed Columns" = Table.RemoveColumns(AddWT,{"ST", "ET", "BT"})
in
    #"Removed Columns"
 
Last edited:
You must log in or register to reply here.
Back
Top