Calculate hour difference per day

[Serdet]

Hi,

I have a dataset similar to the below.

Date	Date  - Time
01/01/2023	01/01/2023 09:00:00
01/01/2023	01/01/2023 09:30:00
01/01/2023	01/01/2023 10:30:00
03/01/2023	03/01/2023 11:00:00
03/01/2023	03/01/2023 14:30:00

I need to calculcate the hours difference between the first and last time but only for each day. 

Looking at the above example the time difference will be 5 as that is the difference between first and last time of each day. This calculation should not span across multiple days.

Your help would be much appreciated.

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNjJR0kjoKBpZWBARApxergVWRMUJGhAbIiY2RFxghFhsjW4VJkAjMpFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Date  - Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Date  - Time", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"Min Time", each List.Min([#"Date  - Time"]), type nullable datetime}, {"Max Time", each List.Max([#"Date  - Time"]), type nullable datetime}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Difference", each [Max Time]-[Min Time],type duration),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date", "Difference"})
in
    #"Removed Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".