Power Query lookup date in Table 1, return value from Table 2 between start and end date

[MurrayBarn]

Hi There

I have searched and searched for a solution but everything I have found is too complicated.

I have imported two tables into Power Pivot in Excel. Table1 has various bits of information including a transaction date. Table2 is a small table that has about ten years as line items with a column for startdate and another column for enddate and then some columns with data relating to that year such as Percentage etc.

I would like to add a Custom Column in Table1 that lookups the transaction date in Table1, finds the row in Table2 where the transaction date is >= startdate & <= enddate and returns a value from another column called Percenatge.

I would prefer the solution to be in the M Query but DAX could work too.

 

[jdellasala]

I'm going to take a stab at this, but be forewarned there may be (probably are) better solutions.
As best as I know you can't do a "lookup" from one table to another in Power Query. I will say it can probably be done in DAX, but that's a bit beyond my skill.
The way to do a lookup is to do a Merge of two tables assuming they both have a common field value (field name does not need to be the same). However, since your Table2 does not have every value that Table1 might have, you'd first need to find a way to get every date possible into Table2. That's where a Calendar Table comes in.
First, create a Calendar Table with all the dates needed. Note in the code below #date needs #date(yyyy, m, d), and you'll want to go from Jan 1 of the earliest year in BOTH tables and extend out as needed.

Calendar

let
    Source = { Number.From( #date(2020, 1, 1))..Number.From( #date(2023,12,31)) },
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}})
in
    #"Changed Type"

Now you want to merge (NEW Merge) Calendar with Table2, expand Table2, FILL DOWN all the columns from Table2, and remove one of the unneeded date columns.

FullTable2

let
    Source = Table.NestedJoin(Calendar, {"Column1"}, Table2, {"date"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"date", "value"}, {"date", "value"}),
    #"Filled Down" = Table.FillDown(#"Expanded Table2",{"date", "value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"date", "C Date"}, {"value", "C Value"}})
in
    #"Renamed Columns"

You now have Table2 with all the possible dates from Table1. I'll call that query (the New Merge) FullTable2.

Now just (New) merge Table1 with FullTable2 and expand the columns needed:

FinalReport

let
    Source = Table.NestedJoin(Table1, {"date"}, FullTable2, {"C Date"}, "FullTable2", JoinKind.LeftOuter),
    #"Expanded FullTable2" = Table.ExpandTableColumn(Source, "FullTable2", {"C Value"}, {"C Value"})
in
    #"Expanded FullTable2"

Now, that new Merge has every row in Table1 with the value for its date from Table2. It's a lot of work to go through compared to a Lookup in a worksheet, but it only has to be done once!
I'll be interested to know if this works for you.

 

[MurrayBarn]

Thanks for having a go. Its a good idea and will give it a go