Forum Discussion

jerryr125's avatar
jerryr125
Helper IV
1 year ago
Solved

Record Occurance - Time Internval

Hi - I have two tables.

I would like to compare the two tables and determine the difference of when record C was entered.

Example:

 

Table01

 

LocationCodeRecordTypeDate
12345A4/24/2025 10am
12345A4/24/2025 11:15am
12345A4/24/2025 1pm
12345A4/24/2025 3pm

 

 

 

Table02

 

LocationCodeRecordTypeDate
12345C4/24/2025 10:20am
12345C4/24/2025 11:40am
12345C4/24/2025 11:45am
12345C4/24/2025 4pm

 

 

 

Table03

 

LocationCodeRecordTypeDateLastRecordA
12345C4/24/2025 10:20am4/24/2025 10am
12345C4/24/2025 11:40am4/24/2025 11:15am
12345C4/24/2025 11:42am4/24/2025 11:15am
12345C4/24/2025 4pm4/24/2025 3pm
Need Help
Show and Tell
  • ronrsnfld's avatar
    ronrsnfld
    1 year ago

    To Create Table03 given Table01 and Table02:

    let
    Source = Table.NestedJoin(Table02,"LocationCode",Table01,"LocationCode","Joined"),
    LastA = Table.AddColumn(Source, "LastRecordA", (r)=> 
        [a=Table.SelectRows(r[Joined], each [Date]<r[Date]),
         b=List.Last(a[Date])][b], type datetime)
in
    LastA

     

    Of course, if you want to show the difference between the two you need to add a column with that calculation:

    let
    Source = Table.NestedJoin(Table02,"LocationCode",Table01,"LocationCode","Joined"),
    LastA = Table.AddColumn(Source, "LastRecordA", (r)=> 
        [a=Table.SelectRows(r[Joined], each [Date]<r[Date]),
         b=List.Last(a[Date])][b], type datetime),
    #"Removed Columns" = Table.RemoveColumns(LastA,{"Joined"}),
    Difference = Table.AddColumn(#"Removed Columns", "TimeInterval-Minutes", each Duration.TotalMinutes([Date] - [LastRecordA]), Int64.Type)
in
    Difference

     

     

     

2 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    1 year ago

    To Create Table03 given Table01 and Table02:

    let
    Source = Table.NestedJoin(Table02,"LocationCode",Table01,"LocationCode","Joined"),
    LastA = Table.AddColumn(Source, "LastRecordA", (r)=> 
        [a=Table.SelectRows(r[Joined], each [Date]<r[Date]),
         b=List.Last(a[Date])][b], type datetime)
in
    LastA

     

    Of course, if you want to show the difference between the two you need to add a column with that calculation:

    let
    Source = Table.NestedJoin(Table02,"LocationCode",Table01,"LocationCode","Joined"),
    LastA = Table.AddColumn(Source, "LastRecordA", (r)=> 
        [a=Table.SelectRows(r[Joined], each [Date]<r[Date]),
         b=List.Last(a[Date])][b], type datetime),
    #"Removed Columns" = Table.RemoveColumns(LastA,{"Joined"}),
    Difference = Table.AddColumn(#"Removed Columns", "TimeInterval-Minutes", each Duration.TotalMinutes([Date] - [LastRecordA]), Int64.Type)
in
    Difference

     

     

     

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Table03:

    let
    Source = Table.NestedJoin(#"Table02", {"LocationCode"}, #"Table01", {"LocationCode"}, "Table01", JoinKind.Inner),
    #"Added Custom" = Table.AddColumn(Source, "LastRecordA", (k)=> List.Max(Table.SelectRows(k[Table01],each [Date]<k[Date])[Date]),type datetime),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Table01"})
in
    #"Removed Columns"