Forum Discussion
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...
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")
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.