Average records before and after given date

• Entries are assumed to be in date order
  • Sort if not
• Join the two tables to get the Repair Date associated with each Car
• Group by Car
  • Select Rows for either before or after the repair date
  • Average the Last 3 or the First 3 (assumes entries are in date order

Read code comments

let

//Read in both tables
//  and remove blank rows
// Change "Source" lines to reflect your actual data sources
    Source = Excel.CurrentWorkbook(){[Name="Output"]}[Content],
    #"Output Table"= Table.TransformColumnTypes(Source,{{"Car", type text}, {"Repair Date", type date}}),

    Source2 = Excel.CurrentWorkbook(){[Name="Repairs"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source2,{{"Car", type text}, {"Failure Date", type date}, {"Repair Cost", Int64.Type}}),

    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

//merge with output table and extract Repair Date column for car type
    Join = Table.NestedJoin(#"Changed Type","Car",#"Output Table","Car","Join",JoinKind.RightOuter),
    #"Expanded Join" = Table.ExpandTableColumn(Join, "Join", {"Repair Date"}, {"Repair Date"}),

//Group by car and compute averages
    #"Grouped Rows" = Table.Group(#"Expanded Join", {"Car"}, {
        
        {"Repair Date", each [Repair Date]{0}, type date},

        {"avg 3 repairs cost before",  (t)=> 
            List.Average(
                List.LastN(
                    Table.SelectRows(t, each [Failure Date] < [Repair Date])[Repair Cost],
                3)
            ), type number},

        {"avg 3 repairs cost after",  (t)=> 
            List.Average(
                List.FirstN(
                    Table.SelectRows(t, each [Failure Date] > [Repair Date])[Repair Cost],
                3)
            ), type number}    
       })
in
    #"Grouped Rows"