Date lookup between ranges in PowerQuery

Hi Anonymous ,
Thanks for jgeddes reply.

Here is my sample data
Raw data

You can try the following code

let
    MRSVersion = #"MRS version",
    RawData = #"Raw data",
    Addcustom = Table.AddColumn(RawData, "MRSVersion", each 
        let
            submissionDate = [Latest Booking Submission Date],
            matchingRow = Table.SelectRows(MRSVersion, each _[MRS Start] <= submissionDate and _[MRS End] >= submissionDate)
        in
            if Table.IsEmpty(matchingRow) then null else matchingRow{0}[MRS version]
    )
in
    Addcustom

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hi Anonymous, check also this:

let
    T1 = [ a = Date.From(DateTime.LocalNow()),
    b = List.Generate(
                ()=> { Date.AddDays(a, -12000), Date.AddDays(a, -11994) },
                each _{0} <= a,
                each { Date.AddDays(_{0}, 7), Date.AddDays(_{1}, 7) } ),
    c = Table.AddIndexColumn(Table.FromRows(b, type table[MRS Start=date, MRS End=date]), "MRS Version", 1)
  ][c],
    T1_SortedRows = Table.Sort(T1,{{"MRS Start", Order.Ascending}}),
    T1_Ad_Date = Table.AddColumn(T1_SortedRows, "Date", each List.Dates([MRS Start], Duration.TotalDays([MRS End]-[MRS Start])+1, #duration(1,0,0,0)), type {date}),
    // This table has to be uniqe per [Date]
    T1_ExpandedDate = Table.ExpandListColumn(T1_Ad_Date, "Date"),
    // Do not add any new columns after this step to T1 (do it before this step if necessary)
    T1_AddKey = Table.AddKey(Table.ReorderColumns(T1_ExpandedDate, {"Date"} & List.RemoveItems(Table.ColumnNames(T1_ExpandedDate), {"Date"})), {"Date"}, true),
    T2 = [ a = Date.From(DateTime.LocalNow()),
    b = Date.AddDays(a, -10000),
    c = Table.FromList(List.Dates(b, Duration.TotalDays(a-b)+1, #duration(1,0,0,0)), Splitter.SplitByNothing(), type table[D=date]),
    d = Table.AddIndexColumn(c, "i", Number.From(Table.FirstN(c, 1){0}[D])),
    e = Table.AddColumn(d, "Date", each Date.From(Number.Round(Number.RandomBetween(d{0}[i], Table.LastN(d, 1){0}[i]))), type date)[[Date]]
  ][e],
    MergedQueries = Table.NestedJoin(T2, {"Date"}, T1_AddKey, {"Date"}, "T1", JoinKind.LeftOuter),
    ExpandedT1 = Table.ExpandTableColumn(MergedQueries, "T1", {"MRS Version"}, {"MRS Version"})
in
    ExpandedT1