Forum Discussion

ANB_13's avatar
ANB_13
Icon for Helper I rankHelper I
2 years ago
Solved

Calculate the difference in minutes between lines

Good morning, I need your help,   I am working on a dataset with a [Date] column in Date & Time format. as following :   I want to create a new column which calculates the difference for s...
Interesting Links
Need Help
Tips and Tricks
Tutorial Requests
  • amustafa's avatar
    amustafa
    2 years ago

    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
    AddMinutesLapsed

     

    If 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/

amustafa's avatar
amustafa
Icon for Solution Sage rankSolution Sage
2 years ago

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
AddMinutesLapsed

 

If 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/