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 ,
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"