Lookup value with condition within a range of dates

[Marsipulami]

Hello Community,

I have been trying for days to implement a lookup function with condition within a range of dates. As a beginner, my knowledge of Power Query is still too limited to solve this task on my own.

In my web searches I have not been able to find any posts that fit my specific case. Several attempts to use chtGPT to solve the problem have also been unsuccessful. .

My task:

I have a table tblLookup which contains all previous contribution rates of different insurances over time; these contribution rates are valid from a certain validity date until the time of the introduction of new contribution rates.

In my table tblData I have to assign the contribution rates of the insurances valid at the time of each period.

tblLookup:

Health Insurance	Valid from Date	Contribution Rate
ABC Inc	01.01.2021	0,90%
BKK Ltd	01.01.2021	1,50%
XYZ Corp	01.01.2021	1,50%
ABC Inc	01.01.2022	1,20%
BKK Ltd	01.01.2022	1,70%
XYZ Corp	01.01.2022	1,80%
ABC Inc	01.01.2023	1,60%
BKK Ltd	01.01.2023	1,80%
ABC Inc	01.01.2024	1,90%
BKK Ltd	01.01.2024	2,00%

tblData:

Period	Employee	Health Insurance
01.12.2021	Ben	ABC Inc
01.12.2021	Dave	BKK Ltd
01.12.2021	Mike	XYZ Corp
01.02.2022	Ben	ABC Inc
01.02.2022	Dave	BKK Ltd
01.02.2022	Mike	XYZ Corp
01.08.2023	Ben	ABC Inc
01.08.2023	Dave	BKK Ltd
01.08.2023	Mike	XYZ Corp
01.02.2024	Ben	ABC Inc
01.02.2024	Dave	BKK Ltd
01.02.2024	Mike	XYZ Corp

Expected Result:

Period	Employee	Health Insurance	Contribution Rate
01.12.2021	Ben	ABC Inc	0,90%
01.12.2021	Dave	BKK Ltd	1,50%
01.12.2021	Mike	XYZ Corp	1,50%
01.02.2022	Ben	ABC Inc	1,20%
01.02.2022	Dave	BKK Ltd	1,70%
01.02.2022	Mike	XYZ Corp	1,80%
01.08.2023	Ben	ABC Inc	1,60%
01.08.2023	Dave	BKK Ltd	1,80%
01.08.2023	Mike	XYZ Corp	1,80%
01.02.2024	Ben	ABC Inc	1,90%
01.02.2024	Dave	BKK Ltd	2,00%
01.02.2024	Mike	XYZ Corp	1,80%

My problem:

The query that I created from the code I found works as long as the date in Period falls within a date range (lower/upper limit).

But: as soon as the date in column Period is greater than the maximum date in column Valid from Date , I get null as query result.

Instead, the currently valid contribution rates based on the last valid date should be displayed in these cases. .

My Code:

let
Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],

//Buffering this table seems to reduce speed of execution from 60 sec to about 20 sec
#"Buffer tblData" = Table.Buffer(Table.TransformColumnTypes(Source,
{{"Period", type date}, {"Employee", type text}, {"Health Insurance", type text}})),

//Read in table 2 and set data types
//Be sure to change `Name` of table to the actual table name in your workbook
Source2 = Excel.CurrentWorkbook(){[Name="tblLookup"]}[Content],
#"Buffer tblLookup" = Table.Buffer(Table.TransformColumnTypes(Source2,
{{"Health Insurance", type text}, {"Valid from Date", type date}, {"Contribution Rate", Percentage.Type}})),
//Join the two tables based on [Health Insurance]
#"Joined Tables" = Table.NestedJoin(#"Buffer tblData", {"Health Insurance"}, #"Buffer tblLookup", {"Health Insurance"},"Joined"),

//filter Joined subtable
#"Filtered Tables" = Table.AddColumn(#"Joined Tables", "Filtered Table", each
let
dt = [Period],
minDT = List.Min(List.Select([Joined][Valid from Date],each _ >= dt)),
maxDT = List.Max(List.Select([Joined][Valid from Date],each _ <= dt))
in
Table.SelectRows([Joined], each [Valid from Date] <= minDT and [Valid from Date] >= maxDT)),

//Lookup Rate
#"Add Rate" = Table.AddColumn(#"Filtered Tables", "Contribution Rate",
each if Table.IsEmpty([Filtered Table]) then null
else [Filtered Table][#"Contribution Rate"]{0}, Percentage.Type),
#"Removed other Columns" = Table.SelectColumns(#"Add Rate",{"Period", "Employee", "Health Insurance", "Contribution Rate"})
in
#"Removed other Columns"

It would be nice if I could receive suggestions for solutions with detailed comments so that I can expand my knowledge of Power Query.

I look forward to your contributions and thank you in advance for your support.

[dufoq3]

Hi, different power query solution here:

Result

let
    TableLookup = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVvDMS1bSUTIw1AMiIwMjQxBHx9JAVSlWJ1rJydtbwackBV2BoY4pVEFEZJSCc35RAW4VWOwwAiswwm0HRIE5HjsgKixw22EMVmCG2w5jQiaYgBXgCQmQAiMdA5CCWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Health Insurance" = _t, #"Valid from Date" = _t, #"Contribution Rate" = _t]),
    TableLookupChangedType = Table.TransformColumnTypes(TableLookup,{{"Valid from Date", type date}, {"Contribution Rate", Percentage.Type}}),
    TableData = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUMzTSMzIwMlTSUXJKzQOSjk7OCp55yUqxOmjSLollqSBV3t4KPiUpmPK+mdkg+YjIKAXn/KICmAIDsAIjXOYjpLGbj5DHZb4FSIExTvPh0jjMh8vjdb8JfvebEHC/CTbzYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Employee = _t, #"Health Insurance" = _t]),
    TableDataChangedType = Table.TransformColumnTypes(TableData,{{"Period", type date}}),
    MergedQueries = Table.NestedJoin(TableDataChangedType, {"Health Insurance"}, TableLookupChangedType, {"Health Insurance"}, "TableLookupChangedType", JoinKind.LeftOuter),
    Ad_ContributionRate = Table.AddColumn(MergedQueries, "Contribution Rate", each List.Last(Table.SelectRows([TableLookupChangedType], (x)=> x[Valid from Date] <= [Period])[Contribution Rate]?), Percentage.Type),
    RemovedColumns = Table.RemoveColumns(Ad_ContributionRate,{"TableLookupChangedType"})
in
    RemovedColumns

[AlienSx · Accepted solution]

Hello, @Marsipulami if your lookup table is sorted by date (Order. Ascending) then you may try to find the last occurrence of "Valid from" date which is less than or equal to Period providing that insurance company values are equal. List.PositionOf is your friend - use optional occurrence parameter as well as custom equationCriteria. But transform your lookup table into the list first in order to apply List.PositionOf like this:

let
    // get your tables. Sort lookup table by date if nessesary. 
    tblLookup = Excel.CurrentWorkbook(){[Name="tblLookup"]}[Content],
    tblData = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
    // transform lookup table into a list of row values
    lstLookup = List.Buffer(Table.ToRows(tblLookup)),
    // List.PositionOf finds a position of an item. 
    // Access that item using list_name{item_position}
    // ones the lookup list item is found - grab 2nd element in that list. 
    // To be on a safe side check for errors just in case item is not found
    // with a help of try ... otherwise (which I did not do)
    // read List.PositionOf documentation for more information.
    col = Table.AddColumn(
        tblData, 
        "Contribution Rate",
        (w) => lstLookup{
            List.PositionOf(
                lstLookup, 
                w, Occurrence.Last,
                (x, y) => (y[Health Insurance] = x{0}) and (y[Period] >= x{1})
            )}{2}
    )
in
    col

[Anonymous]

hi, how would you do this query in DAX as a new column? i have a similar task but i need this in DAX

[Marsipulami]

Hello AlienSx,
great, that's exactly what I was looking for.
Thank you very much for your quick and valuable support.

With best regards. 😂

Marsipulami