Using mCODE Subtract a column of minutes from a column with a date stamp

[rockingmark]

Hi,  I have a table with a column of date/time timestamps called "Time Stamp" and I want to subtract a duration in minutes (on the same row in column Minutes) from the timestamp.  How can this be done? Desired column shown below.  Many thanks

		Desired Column from mcode calculation
Time stamp	Minutes	timestamp column subtracted Minutes column
31/08/2023 17:59:42	12.5	31/08/2023 17:47:12
31/08/2023 09:56:04	13.00	31/08/2023 09:43:04
31/08/2023 19:24:37	1.25	31/08/2023 19:23:22

[gmsamborn · Accepted solution]

Hi @rockingmark 

If you divide [Minutes] by (24 * 60), the result is the fraction of a day which converts to a duration.

let
    Source = SourceTable,
    #"Added Custom" = Table.AddColumn(Source, "Days", each [Minutes] / ( 24 * 60 )),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Days", type duration}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "New Timestamp", each [Time stamp] - [Days]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"New Timestamp", type datetime}})
in
    #"Changed Type1"

Subtract minutes from timestamp.pbix

[rockingmark]

Cool Thanks, I was missing that you can convert minutes to duration so your example massively helped me 

[HotChilli]

Do you want the minutes column e.g. 12.5 to be a duration of 12 minutes 30 secs?

If so,

Number.RoundDown([Minutes])

will give the whole minutes.

60 * Number.Mod([Minutes],1)

will give the seconds.

You use those to construct a #duration() https://learn.microsoft.com/en-us/powerquery-m/sharpduration and subtract it from the Time Stamp column