Forum Discussion
Calculate the difference in minutes between lines
Hi, create a blank query and paste the following code to see how you can create a calulated column for minutes lapsed between rows.
let
StartDateTime = #datetime(2024, 1, 1, 0, 0, 0),
EndDateTime = #datetime(2024, 1, 2, 0, 0, 0),
DurationInMinutes = 5,
Source = List.Generate(
() => StartDateTime,
(DateTime) => DateTime < EndDateTime,
(DateTime) => DateTime + #duration(0, 0, DurationInMinutes, 0)
),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing(), {"DateTime"}),
ChangedType = Table.TransformColumnTypes(TableFromList, {{"DateTime", type datetime}}),
SortedTable = Table.Sort(ChangedType, {{"DateTime", Order.Ascending}}),
AddIndex = Table.AddIndexColumn(SortedTable, "Index", 0, 1, Int64.Type),
AddMinutesLapsed = Table.AddColumn(AddIndex, "Minutes Lapsed", each if [Index] = 0 then 0 else Duration.TotalMinutes([DateTime] - (AddIndex{[Index]-1}[DateTime])), type number)
in
AddMinutesLapsedIf I answered your question, please mark this thread as accepted and Thums Up!
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/
Hi ANB_13,
You can find 2 versions of code here:
//Calculate the difference in minutes between lines
//https://community.fabric.microsoft.com/t5/Power-Query/Calculate-the-difference-in-minutes-between-lines/m-p/3670461#M120753
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDSNzDUNzIwMlYwtzIwVYrVQRMzNMQUMzJDF7O0MjZSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
v1_Slower_AddedIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
v1_Slower_Difference = Table.RemoveColumns(Table.AddColumn(v1_Slower_AddedIndex, "Difference d:hh:mm:ss", each try [Date] - v1_Slower_AddedIndex{[Index]-1}?[Date] otherwise null, type duration), {"Index"}),
v2_Faster_Difference =
Table.RenameColumns(
Table.FromRecords(
[timesList = List.Buffer(#"Changed Type"[Date]),
lg =
List.Generate(
()=> [ x = 0, DateTime = timesList{0}, Difference = null ],
each [x] < List.Count(timesList),
each [ x = [x]+1, DateTime = timesList{x}, Difference = DateTime - [DateTime] ],
each [ [DateTime], [Difference] ]
)
][lg],
type table[DateTime = datetime, Difference = duration]
),
{{"DateTime", "Date Time"}, {"Difference", "Difference d:hh:mm:ss"}}
)
in
v2_Faster_Difference
Good morning,
Where do i use this code ?
Thank you