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!
User | Count |
---|---|
90 | |
90 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
147 | |
92 | |
70 | |
58 |