Forum Discussion

Jeff_PowerBI's avatar
Jeff_PowerBI
Frequent Visitor
2 years ago

Power Query: Lookup data from other table with date condition

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.

Need Help
Tips and Tricks
Tutorial Requests

3 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    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's avatar
      Jeff_PowerBI
      Frequent Visitor
      2 years ago

      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.

  • spinfuzer's avatar
    spinfuzer
    Solution Sage
    2 years ago

     

    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"