Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
I_dont_know_M
Regular Visitor

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

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.

9 REPLIES 9
I_dont_know_M
Regular Visitor

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
Regular Visitor

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 TimeRun time (the column I wanted to create, in minute)
2022-11-21 3:00:00 PM0
2022-11-21 3:01:00 PM1
2022-11-21 3:02:00 PM2
2022-11-21 3:03:00 PM3

Hi,

This calculated column formula works

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

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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"

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
Super User
Super User

@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
Super User
Super User

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.

bolfri_0-1673485595598.png

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"

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.