PowerQuery - convert text 2400 to time and calculate

Question

I am working on an attendance report. The raw data displays time in format of "0800", when handling shift schedule, Power Query cannot calculate the work hour.

Example:

In Time	Out Time
0800	1800
2409	3200

Expected Output for Work Hour:

In Time	Out Time	Work Hour
0800	1800	1000
2409	3200	0751

However, Power Query cannot convert 2400 to time. I have tried below actions.

1. Initial In and Out time is in Type Text, Change to time -> Expression. Error: We couldn't parse the input provided as a Time value
2. Change In and Out Time to Number, then change to time -> Expression. Error: We couldn't parse the input provided as a Time value
3. Change to duration, result is not correct -> expected: 751, actual: 791

I versuche in excel by using =TEXT(@InTime,"00:00")+0 to convert to time, it can recognize and calculate perfectly. Wonder if there is anything like that in Power Query.

Thanks in advance.

Answer 1

The Power Query time data type does not recognize hours > 24. You need to convert your values to durations

• Intially type as Integer
• Convert each integer to a duration
• subtract the two durations
• convert the resulting durations back to an integer
• convert all the values to four digit text strings

Add two custom functions
Named as per the bold type

fnIntegerToDuration

(a as number)=>

let
    hrs = Number.IntegerDivide(a,100),
    mins = Number.Mod(a,100)
in 
   #duration(0,hrs,mins,0)

fnDurationToInteger

(a as duration)=>
    let 
        dys = Duration.Days(a) * 2400,
        hrs = Duration.Hours(a) * 100,
        mins = Duration.Minutes(a)
    in 
        dys+hrs+mins

Then use this for your main Code, changing the Table Name as needed:

let
    Source = Excel.CurrentWorkbook(){[Name="Shifts"]}[Content],

//Type as integer initially
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"In Time", Int64.Type}, {"Out Time", Int64.Type}}),

    #"Add Duration" = Table.AddColumn(#"Changed Type", "Work Hour", each 
        fnDurationToInteger(
            fnIntegerToDuration([Out Time])-fnIntegerToDuration([In Time])), 
        Int64.Type),

//Return to four digit text strings for formatted output
    #"Convert to Text Strings" = Table.TransformColumns(#"Add Duration", 
        List.Transform(Table.ColumnNames(#"Add Duration"), (li)=> {li, each Number.ToText(_,"0000"), type text}))
    

in
    #"Convert to Text Strings"

Output