Record Occurance - Time Internval

[jerryr125]

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

LocationCode	RecordType	Date
12345	A	4/24/2025 10am
12345	A	4/24/2025 11:15am
12345	A	4/24/2025 1pm
12345	A	4/24/2025 3pm

Table02

LocationCode	RecordType	Date
12345	C	4/24/2025 10:20am
12345	C	4/24/2025 11:40am
12345	C	4/24/2025 11:45am
12345	C	4/24/2025 4pm

Table03

LocationCode	RecordType	Date	LastRecordA
12345	C	4/24/2025 10:20am	4/24/2025 10am
12345	C	4/24/2025 11:40am	4/24/2025 11:15am
12345	C	4/24/2025 11:42am	4/24/2025 11:15am
12345	C	4/24/2025 4pm	4/24/2025 3pm

[lbendlin]

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"

[ronrsnfld · Accepted solution]

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