let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZDLasMwEEV/ZfC6IpL8UKyd8uimNBgS0kXIQnWnsUCRjGx1U/rvVWIa2q6zGjgw98ydwyHjohDZQ7Z+UUwwCWrxTDjlOVHWwhZP17lTZOndO9muYGH8iG0HjQ6jw2DcCR59iGf4bJrVVwq6LtOasBKYkMU8IfWkGM0l7DoMusc4mvavY6/b1jgcyPqsjQWyiWGMASHds482afSrReh9H60ejXfDP5sgjAGrZFHdv0lJGAU6l+XlSxPlhPFUEVii+dSP8puQ34QbjMH/dFpiwA+0oN0bJAH0k3boTP/bOGVXwGpZ1AmJGaezyyXZ8fgN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Record ID" = _t, #"Most Recent Martech Campaign Current Value" = _t, #"Most Recent Martech Campaign Change Date" = _t, #"Most Recent Martech Campaign Previous Value (1)" = _t, #"Most Recent Martech Campaign Change Date (1)" = _t, #"Most Recent Martech Campaign Previous Value (2)" = _t, #"Most Recent Martech Campaign Change Date (2)" = _t, #"Most Recent Martech Campaign Previous Value (3)" = _t, #"Most Recent Martech Campaign Change Date (3)" = _t, #"Most Recent Martech Campaign Previous Value (4)" = _t, #"Most Recent Martech Campaign Change Date (4)" = _t, #"MQL Date" = _t]),

// Sort the rows by 'Email' and 'Most Recent Martech Campaign Change Date'
    SortRows = Table.Sort(Source, {{"Record ID", Order.Ascending}, {"Most Recent Martech Campaign Change Date", Order.Ascending}}),

    // Group the sorted table by 'Email' and 'Became an MQL Date'
    GroupByIdAndMQLDate = Table.Group(SortRows, {"Record ID", "MQL Date"}, {{"AllData", each _, type table}}),
    
// Apply the custom function to find the closest date and corresponding campaign across all pairs
ApplyCustomFunction = Table.TransformColumns(GroupByIdAndMQLDate, {"AllData", each 
    let
        currentRow = _,
        mqlDate = currentRow[#"MQL Date"]{0},

        // Combine all date and value pairs into lists
        allDates = List.Combine({
            currentRow[#"Most Recent Martech Campaign Change Date"],
            currentRow[#"Most Recent Martech Campaign Change Date (1)"],
            currentRow[#"Most Recent Martech Campaign Change Date (2)"]
        }),
        
        allValues = List.Combine({
            currentRow[#"Most Recent Martech Campaign Current Value"],
            currentRow[#"Most Recent Martech Campaign Previous Value (1)"],
            currentRow[#"Most Recent Martech Campaign Previous Value (2)"]
        }),

        // Sort the combined list of dates
        sortedDates = List.Sort(allDates),

        // Find the closest date to MQL Date
        closestDate = List.Last(List.Select(sortedDates, each _ <= mqlDate)),

        // Find the index of the closest date in the sorted list
        indexClosestDate = List.PositionOf(sortedDates, closestDate),
        
        // Debug info: Count of all dates and values
        countAllDates = List.Count(allDates),
        countAllValues = List.Count(allValues),

        // Pick the corresponding value based on the index
        closestValue = 
            if indexClosestDate <> -1 then 
                if allValues{indexClosestDate} = null then "No corresponding previous value" else allValues{indexClosestDate} 
            else null
    in
        [ClosestDate = closestDate, ClosestValue = closestValue, DebugIndex = indexClosestDate, DebugCountAllDates = countAllDates, DebugCountAllValues = countAllValues]
}),



    // Expand the custom columns into the main table
    ExpandCustomColumns = Table.ExpandRecordColumn(ApplyCustomFunction, "AllData", {"ClosestDate", "ClosestCampaign","DebugIndex","DebugCountAllDates","DebugCountAllValues"}, {"ClosestDate", "ClosestCampaign","DebugIndex","DebugCountAllDates","DebugCountAllValues"})

in
    #"ExpandCustomColumns"