How to structure time and date in PowerQuery

[Waimea]

I want to calculate time differences in PowerPivot/PowerQuery/Power BI but I can't get it to work between days.

In excel I could use 1904 dates and it somewhat works.

How should I structure date & time data to correctly calculate time differences between dates and hours with dates and hours.

Arrival time	Exit time	Time
2020-03-24 21:15:59	2020-03-25 04:14:00	##### is displayed instead of actual time difference
2020-03-23 07:00:00	2020-03-23 09:00:00	02:00:00 hours

I have tried with date, date and time but it doesn't work.

So I would be glad if someone could show me how to calculate time differences correctly using DAX or PowerQuery?

 

[GraH]

Hi, I believe it is doable with the UI in PQ.
Select both columns (Exit before Arrival while holding CTRL key) and in the Add Column Ribbon select Date and from the dropdown Subtract Days or for time difference use Time drop down instead.
Make sure your columns are defined as date/datetime data types.

 

[Waimea]

Hi,

thank you for your reply.

I found the subtract and duration functions in the time, duration menu.

It says "-hours" for values that are between days and I am not sure how to read the - sign? ( I also think that the time after the minus sign is wrong)

I want to be able to subtract the dates and get the correct number of hours between two dates with different times in them.

Should I try to divide the date and times into smaller pieces or attack this problem some other way?

 

[sandy666]

is that what you want?

Arrival time	Exit time	Subtraction	Total Hours
24/03/2020 21:15	25/03/2020 04:14	0.06:58:01	6.966944444
23/03/2020 07:00	23/03/2020 09:00	0.02:00:00	2
23/02/2020 07:00	25/03/2020 04:14	30.21:14:00	741.2333333

Subtraction format is: d.hh:mm:ss

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Arrival time", type datetime}, {"Exit time", type datetime}}),
    Subtract = Table.AddColumn(Type, "Subtraction", each [Exit time] - [Arrival time], type duration),
    TotalHours = Table.AddColumn(Subtract, "Total Hours", each Duration.TotalHours([Subtraction]), type number)
in
    TotalHours

you can round TotalHours as you wish
or choose one as the end result

 

[GraH]

Hi,
I believe you selected your columns in the wrong order if you have a "-" (minus) sign in your result.

See what sandy posted. I wanted to suggest the same

 

[Norie]

How about a custom column with this simple formula?

= [Exit Time]-[Arrival Time]

 

[Waimea]

Hi,

thank you for your replies.

Sandy666's method does what I want to do.

 

[sandy666]

you can do that with a custom column

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Hours = Table.SelectColumns(Table.TransformColumns(Table.TransformColumnTypes(Table.AddColumn(Source, "Hours", each ([Exit time]-[Arrival time])*24),{{"Hours", type number}}),{{"Hours", each Number.Round(_, 2), type number}}),{"Hours"})
in
    Hours

Arrival time	Exit time		Hours
24/03/2020 21:15	25/03/2020 04:14		6.97
23/03/2020 07:00	23/03/2020 09:00		2
23/02/2020 07:00	25/03/2020 04:14		741.23

 

[sandy666]

or just Custom Column with:
Number.Round(Duration.TotalHours([Exit time]-[Arrival time]),2)

 

[Waimea]

Hi Sand666,

I really like the last solution, simple but elegant!

Thank you!