Forum Discussion

ANB_13's avatar
ANB_13
Helper 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 successive rows. The result of the 5 new lines should be:
5. How can i do ?

 

Thank you very much 

Regards

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/

5 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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

     

    • ANB_13's avatar
      ANB_13
      Helper I
      2 years ago

      Good morning, 

       

      Where do i use this code ? 

       

      Thank you 

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    dufoq3 this must be accompanied by v3 with list shift 😁

    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]),
    dt = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
    diff_lst = List.Buffer(List.Zip({dt[Date], {List.First(dt[Date])} & List.RemoveLastN(dt[Date], 1)})),
    diff_col = List.Transform(diff_lst, (x) => Duration.Minutes(x{0} - x{1})),
    tbl = Table.FromColumns(Table.ToColumns(dt) & {diff_col}, Table.ColumnNames(dt) & {"diff"})
in
    tbl

     

  • amustafa's avatar
    amustafa
    Solution 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/