Forum Discussion
Anonymous
6 years agoNot applicable
Difference between 2 times in the same column
Hi, I am trying to create a new column that displays the how many minutes have passed between each row in the [Time] Column. Basically, I just need to subtract the bottom row from the above row ...
Anonymous
6 years agoNot applicable
Anonymous Thanks for the input. How would I go about doing that in the power query editor?
Anonymous
6 years agoNot applicable
"Easily" is the answer 🙂 I'll post a solution to this.
- Anonymous6 years agoNot applicable
Here's the M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdJLbsMwDATQu3idAEPqY3l2bXe9QpArdNf7dxQXThXVBLJQ4AeJHPJ2W96WywK7fn5/XR0O/bONqdKsH5f75T/iRohsMSmFaAExsDiRR+IDScyZyXVMJwSNpRElIOpIb+UUk6xfi0hTx/TnQ+9zRyrE/+YyE6xMG5ECoqZFvEYk09NE/JWoaRvLHaOrym2f0SlJLLbP6IxY6ZM2j0jWd2LVsQYPgR6S1m8p9SAfcy6VRWPEkctMlJtmEJJebtXiRQSPW2wkY7no+2LPrZtJj05jtIhsLCuBiKyPSeeAQPvU9nRPbzGqHIRE6/2bbj0j2l3tiwVEY+zRtYhoMXHccv8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Material = _t, Date = _t, Time = _t, Shift = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", type text}, {"Date", type date}, {"Time", type time}, {"Shift", type text}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Material", "Date", "Shift", "Time"}), #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Material", Order.Ascending}, {"Date", Order.Ascending}, {"Shift", Order.Ascending}, {"Time", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1), #"Reordered Columns1" = Table.ReorderColumns(#"Added Index",{"Index", "Material", "Date", "Shift", "Time"}), #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Index", Int64.Type}}), #"Added Custom" = Table.AddColumn( #"Changed Type1", "PrevTime", each List.First( Table.SelectRows( #"Changed Type1", (r) => r[Material] = [Material] and r[Date] = [Date] and r[Shift] = [Shift] and r[Index] = [Index] - 1 )[Time], null ) ), #"Added Custom1" = Table.AddColumn( #"Added Custom", "TimeDiff", each if [PrevTime] <> null then // this rounds a number such as // 9.822 to just 9. You can remove // the Int32.From function to get // the exact number of minutes // with fractional parts. Int32.From(Duration.TotalMinutes([Time] - [PrevTime])) else null ), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"PrevTime", "Index"}) in #"Removed Columns"- Anonymous6 years agoNot applicable
Anonymous So I am quite new to Power BI, so I apologize for the ignorance haha. Do I just create a custom column and paste all of that code into the box?
- Anonymous6 years agoNot applicable