Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
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"
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |