PDA

View Full Version : Time problem



Paul_Christie
2017-04-12, 11:24 AM
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

NBVC
2017-04-12, 12:56 PM
Are you able to attach a sample file showing this problem?

Paul_Christie
2017-04-12, 01:29 PM
NBVC

If you PQ to the attached you should be able to replicate/solve my problem.

Paul

NBVC
2017-04-12, 01:56 PM
I applied this formula in column F and I don't see any errors...

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

Paul_Christie
2017-04-12, 02:04 PM
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

Paul_Christie
2017-04-12, 02:09 PM
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

NBVC
2017-04-12, 02:36 PM
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..

Paul_Christie
2017-04-12, 02:42 PM
Such is life, thanks for trying.

Paul

Ken Puls
2017-04-12, 04:15 PM
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!

Paul_Christie
2017-04-12, 04:46 PM
Ken

I'm trying to attach a cut down version of one of the original text files but I'm having a problem. Might I have hit some sort of limit.

Paul

Paul_Christie
2017-04-12, 05:44 PM
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

Ken Puls
2017-04-12, 07:38 PM
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. :)

Paul_Christie
2017-04-12, 07:50 PM
Ken

The text file in my last reply exhibits the problem, I've just tested it.

Regards
Paul

Ken Puls
2017-04-12, 07:53 PM
Doh! Didn't see page 2 of the thread. Looking now.

Ken Puls
2017-04-12, 08:18 PM
Hey Paul,

So I did this:


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.

Paul_Christie
2017-04-12, 08:39 PM
Ken,

Thanks for that, it works for me. I'll try and understand it in the morning. Time to shut laptops down, I've had 2 on the go for last 3 hours.

Paul

MarcelBeug
2017-04-13, 03:05 AM
Should you prefer time format for the result, then you can add #time(0,0,0) as in:


#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #time(0,0,0)+([FinishTime]-[StartTime]-[BreakDuration]))

Paul_Christie
2017-04-13, 08:31 AM
Marcel,

Thanks for the additional info.

Paul

Comfy
2017-04-18, 02:45 PM
How did I miss page 2???



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"

Paul_Christie
2017-04-18, 02:55 PM
Thanks for that, always good to see several different ways of achieving the same objective.