March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
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.
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 |
Hi,
This calculated column formula works
Column = 60*24*(Data[Date and Time]-MIN(Data[Date and Time]))
Hope this helps.
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"
In that case @HotChilli gave you a solution to add an index column starts from 0.
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.
@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
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"
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
124 | |
87 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
153 | |
97 | |
79 | |
69 |