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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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