Date lookup between ranges in PowerQuery

[crn009]

I have a data set uploaded to my PowerQuery called "MRS version"
Here, there is a table looking like this: 

MRS Start	MRS End	MRS version
01/01/2023	08/02/2023	1
09/02/2023	05/03/2023	2
06/03/2023	03/04/2023	3

In another data set i have uploaded called "Raw data" i have a column called "Latest Booking Submission Date". 

I would like to know which version of the MRS connects to the date in "Latest Booking Submission Date". 

Example: If Latest Booking Submission Date is the 15/02/2023 then it returns that this is MRS Version 2. 

I have tried multiple solutions which has resulted in the entire program to crash, so i am looking for a 'simple' and not to heavy solution to this. Hope there is someone that can help me!

[v-heq-msft · Accepted solution]

Hi @crn009 ,
Thanks for jgeddes reply.

Here is my sample data
Raw data

Latest Booking Submission Date

2/15/2023

2/1/2023

4/1/2023

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

[dufoq3 · Accepted solution]

Hi @crn009, 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

[AlienSx]

1. Rename "MRS Start" column to "Latest Booking Submission Date"

2. Combine 2 tables (MTS_version & Raw_data)

3. Sort resulting table by "Latest Booking Submission Date", then by "MRS End" (both Order.Ascending)

4. Fill down "MRS version" column

5. Filter out rows from MRS_version table (MRS End is null) and remove "MRS End" column

[v-heq-msft · Accepted solution]

Hi @crn009 ,
Thanks for jgeddes reply.

Here is my sample data
Raw data

Latest Booking Submission Date

2/15/2023

2/1/2023

4/1/2023

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

[crn009]

Hi Albert He,
Thank you very much for your help!
Although, i have been down the path you mention here and many others - the problem keeps being that by dataset is very heavy, so the PowerBI is crashing over and over again after loading approx 10k rows.. 

Do you have any solution that can prevent that from happening? 

[v-heq-msft]

Hi @crn009 ,
You can try these following methods:

First, if possible, try to reduce the amount of data loaded. For example, load only the necessary columns or use filters to reduce the number of rows. Or process the dataset in smaller parts. You can use parameterized queries or partitioning strategies to load data in segments. Of course, you can also try to increase your computer's memory if you can to improve processing efficiency.

Second, you can use the Table.Buffer function to cache the table data and reduce the overhead of repeated calculations.

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

Best practices when working with Power Query - Power Query | Microsoft Learn

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

[jgeddes]

You can try the following logic to see if it will work in your situation.

Add a column with this code (changing it to match your column/table names).

= Table.AddColumn(#"Changed Type", "MRS Version", each let rowDate = [subDate] in Record.Field(Table.SelectRows(Table, each rowDate >= [MRS Start] and rowDate <= [MRS End]){0}, "MRS version"))

This code should be added in the table that you are looking to return the 'MRS Version' to.

#"Changed Type" is the name of the previous step in the table.

[subDate] is the name of the column in the table that has the date value you are wanting to filter the date table with. (You referred to it as 'Latest Booking Submission Date'.)

In the 'Table.SelectRows() function...
Table is the name of your date table.

[MRS Start] & [MRS End] are the names of the respective columns in the date table.

{0} returns the first row of the resulting table.

Record.Field() takes the first row of the filtered table and returns the value from the 'MRS version' column.

Hope this gets you pointed in the right direction.