PQ : Number format in duration

[joslaz]

Hey guys!

How do I get the following number format in a clean format to calculate the duration?

The numbers consist of hr min sec - two items each.


hyundai accent 2012 0 to 60


The problem now is that the close of the day is defined as 240000.
The second problem is that some minutes and seconds have errors.
So for example the number 128878 would actually be 125959.
So every minute or second> = 59 = 59.


How can this be implemented in Power Query?
greeting
Joshua

 

[Redoute]

Assuming Start and End are in a table named "Values":

let
    source = Excel.CurrentWorkbook(){[Name="Values"]}[Content],
    tm = each let
        h = Number.IntegerDivide(_, 10000),
        m = Number.Mod(Number.IntegerDivide(_, 100), 100),
        s = Number.Mod(_, 100)
    in #time(Number.Mod(h, 24),
             List.Min({m, 59}),
             List.Min({s, 59})),
    result =
    Table.SelectColumns(
        Table.AddColumn(
            Table.AddColumn(
                Table.AddColumn(
                    Table.AddColumn(source, "tmstart", each tm([Start])),
                    "tmend", each tm([End])),
                "dur1", each [tmend] - [tmstart]),
            "Duration",
            each if [dur1] <= #duration(0, 0, 0, 0) then
                 [dur1] + #duration(1, 0, 0, 0) else [dur1]),
        {"tmstart", "tmend", "Duration"})
in
    result

In Row 4 of your examples I think you have a typo, or you should add a rule that changes "300000" in 3:20:00. The code above reads "300000" as 6:00:00.

 

[joslaz]

Thanks!

It works great!
Thats right, the 4th row of my example is false (duration).

Could you please edit the code in that way there will be now column removed?
The original data has 10 columns and round about 500k rows.


Thanks a lot!

 

[Redoute]

Just leave Table.SelectColumns out, or replace it with Table.RemoveColumns to remove temporary columns:

let
    source = Excel.CurrentWorkbook(){[Name="Values"]}[Content],
    tm = each let
        h = Number.IntegerDivide(_, 10000),
        m = Number.Mod(Number.IntegerDivide(_, 100), 100),
        s = Number.Mod(_, 100)
    in #time(Number.Mod(h, 24),
             List.Min({m, 59}),
             List.Min({s, 59})),
    result =
    Table.AddColumn(
        Table.AddColumn(
            Table.AddColumn(
                Table.AddColumn(source, "tmstart", each tm([Start])),
                "tmend", each tm([End])),
            "dur1", each [tmend] - [tmstart]),
        "Duration",
        each if [dur1] <= #duration(0, 0, 0, 0) then
             [dur1] + #duration(1, 0, 0, 0) else [dur1])
in
    result

 

[joslaz]

Ok, cool
It works great!

Unfortunately I have two kinds of 00:00:00.
There is sometimes the end of a day with 240000 defined.
For example

Start = 200000
End = 240000

Your code defines 240000 as 00:00:00
The result for the duration is then -04:00:00.

Hoe can we manage this?
Do you have any idea?

Best regards

 

[Redoute]

The script as is should never return negative durations. For Start=200000 and End=240000 I get Duration=0.04:00:00. Only the temporary column dur1=-20:00:00.

 

[joslaz]

Sry, my mistake.

What ist about 60000 - 60000 or 0 - 0
The duration is 1.00:00:00. But this is not correct. The duration should be 0.00:00:00.
How can I fix this?

Best regards!