I have a datetime column, want to add a column that counts the time since the starting datetime

[I_dont_know_M]

I'm new to Power Query and M. I have a column that's in datetime type, it starts from 2022-11-21 3:00:00 PM, and ends at 2022-11-21 5:00:00 PM, with one minute increment.  I want to add a custom column that shows the time lasted since the start. So I wish the result to be {0, 1, 2, 3,...} in minutes in the duration type. The name of the datetime column is "Date and Time", previous step is "Added Index". My function goes:

= Table.AddColumn(#"Added Index", "Run time", each [Date and Time]-[Date and Time]{1})

I got message:

Expression.Error: We cannot convert the value #datetime(2022, 11, 21, 15, 0, 0) to type List.
Details:
Value=2022-11-21 3:00:00 PM
Type=[Type]

I'm keeping the "Date and Time" column in datetime type so if the experiment runs pass mid night, it can still calculate the duration.

I have searched the internet for hours, I came to here for your help, please, help me, many thanks in advance.

[I_dont_know_M]

Hi All, thanks to a 2019 solution published by @Nolock , I have now solved the error.

The syntex has been changed from

=Table.AddColumn(#"Added Index", "Run time", each [Date and Time] - [Date and Time]{0})

to 

=Table.AddColumn(#"Added Index", "Run time", each [Date and Time] - #"Added Index"[Date and Time]{0})

To paraphrase what @Nolock said in the 2019 post, in the wrong code, within each [Date and Time] - [Date and Time]{0}, the each affects both [Date and Time]s, as it's looping through a row, it pulls the value of the [Date and Time] of that row, because a value is not a list, [Date and Time]{0} shows the type error. However, when the previous step's table reference is added, it interpretes the [Date and Time] as a column, then the index argument {0} works.

Nonetheless, the performance of this step is very poor, it took a long time to calculate.

[I_dont_know_M]

Hi Bofri, my data is in the Power Query editor, the first column is in datetime format, I want to add a second column in the next step, the second column shows the duration since the first item of the datetime column. It looks like below:

Date and Time	Run time (the column I wanted to create, in minute)
2022-11-21 3:00:00 PM	0
2022-11-21 3:01:00 PM	1
2022-11-21 3:02:00 PM	2
2022-11-21 3:03:00 PM	3

[Ashish_Mathur]

Hi,

This calculated column formula works

Column = 60*24*(Data[Date and Time]-MIN(Data[Date and Time]))

Hope this helps.

[I_dont_know_M]

Thanks Ashish, I will try this at work tomorrow, is there no way to refer to the first item of the datetime in M?

[Ashish_Mathur]

You are welcome.  Here are 2 M codes which will create another column showing the first date and time stamp in each cell.  Though i am satisfied with neither because there are just way too many steps but i am sure someone will pitch in and offer a much better solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date and Time", type datetime}}),
    Custom1 = Record.ToTable(#"Changed Type"{0}),
    Custom2 = #"Changed Type",
    #"Added Custom" = Table.AddColumn(Custom2, "Custom", each Custom1[Value]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date and Time", type datetime}}),
    Custom1 = #"Changed Type"{0},
    Custom2 = #"Changed Type",
    #"Added Custom" = Table.AddColumn(Custom2, "Custom", each {Custom1}),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Record.Field([Custom]{0}, "Date and Time")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"

[bolfri]

In that case @HotChilli gave you a solution to add an index column starts from 0.

[I_dont_know_M]

Can you please elaborate on this solution? I don't really know M language, new to the field. I would really like to see how M language can refer to a certain item on a column, in Excel, I'd use INDEX function, here I'm lost, I get the "cannot convert to List" error.

[HotChilli]

@bolfri  I think @I_dont_know_M  has one column with the datetime in it (each value 1 minute greater than the previous)and they want an 'elapsed time' column to be added.

Maybe there's more to it but adding an index from 0 would do it

[bolfri]

I am sorry, but I don't understand.  You have one or two columns? If you have Start and End in datetime format then you can just show difference as duration.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI1NNQ1MlQwtjIwACKFAF8lHWRxU4R4bCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type datetime}, {"End", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each [End] - [Start])
in
    #"Added Custom"