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   LocationCode RecordType Date 12345 A 4/2...
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

     

     

     

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"