Power Query: Lookup data from other table with date condition

[Jeff_PowerBI]

Hi.

I have 2 tables and needed to get the data from table 2 with ceratin condition based on date.

It must return the column 'Cost' in table 1 if the date in Table 1 is greater than or equal to date in Table 2. See below sample.

This can be achieved thru SQL but needed a simplified way in Power Query.

Your help is greatly appreciated.

[spinfuzer]

Hi @Jeff_PowerBI ,

This should take care of your null ID issue if you want to use the below instead.  You could also insert a dummy row in table2 with a null id and very early date and do the fill down method.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    previousStep = Table.TransformColumnTypes(Source,{{"TRANSDATE", type date}, {"id", Int64.Type}}),
    #"Added Custom" = 
        let
            buff = Table.Buffer(Table.Sort(Table2, {{"id", Order.Ascending}, {"trans_date", Order.Descending}}))
            // must sort dates in descending order for Occurence.First below to work
        in
            Table.AddColumn(previousStep, "cost", each 
                try 
                    buff[cost]{
                        Table.PositionOf(
                        buff, 
                        _, 
                        Occurrence.First, 
                        (v,t) => t[id] = v[id] and v[trans_date] <= t[TRANSDATE]
                        // v is the table2 with costs and t is from table1
                        )
                    } 
                otherwise 0
            )
in
    #"Added Custom"

[AlienSx]

Hi, @Jeff_PowerBI 

    append = Table.AddColumn(Table1, "data", each true) & Table.RenameColumns(Table2, {"trans_date", "TRANSDATE"}),
    sort = Table.Sort(append,{{"TRANSDATE", Order.Ascending}, {"cost", Order.Descending}}),
    fd = Table.FillDown(sort,{"cost"}),
    filtered = Table.SelectRows(fd, each ([data] = true)),
    rc = Table.RemoveColumns(filtered, "data")

[Jeff_PowerBI]

Hi @AlienSx,

Thank you so much for the solution.

It worked on majority of IDs but since it used fill down, some IDs have data mismatch particularly if the ID has null cost.

Trying to work out how to solve this problem.

Thank you.