Forum Discussion
Derive 24 hour shift date. Overlapping calendar date
I need a query to derive shift date from my data table. The expected Shift date is what i have highlighted in yellow, column shift date.
If you subtract from all the datetimes, the results will be the same, and the equation is simpler.
eg. 4/6/2025 6AM - 6hrs => 4/6/2025
5/6/2025 5:59AM - 6hr => 4/6/2025
No, i only need to subtract for the time between 12 mindnight to 6 am of next morning
ronrsnfld
Super User
Super UserUsing your other data set, and merely subtracting six hours from each datetime:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdA7DoRADAPQq6CpkRjng3bS7QGQtkfc/xrLlGC3T4mc+DwbNmyWbW1RhuV7tGt9YJjCJMzqLtB4/caPwiHQ1brE6Ar5o6zk9F0dP8pDoCgEvSA1OR8o7EpTqBXG8nvrDGN0MFp1HrVZNWM4oc8G3hjz0onXHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Time = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}}),
#"Add Shift Date" = Table.AddColumn(#"Changed Type","Shift Date",
each Date.From(([Date] & [Time]) - #duration(0,6,0,0)), type date)
in
#"Add Shift Date"
Please note that the date format is mm/dd/yyyy since I am in the US.