Select first row metting condition

[chalmers]

Hi, I have a table (PRODUCT) with product data and a creation datetime. I have a second table (PERIOD) with year, sale period, and start / end date of those period.

I try to create a column in PRODUCT containing the row of PERIOD when my creation datetime is between start date and end date. Right now, I have this formula ("period" is a step before where I ref the second table):

Table.AddColumn(#"Step before", "PERIOD", each Table.SelectRows(period, (v_period) => ([DATETIMECREATION] >= v_period[STARTDATE] and [DATETIMECREATION] <= v_period[ENDDATE])))

I have the good result, but since I have ~400 row in PERIOD and maybe 4 000 000 rows in product, is there a better way ? I've try a conditional join but without sucess. And Table.First don't take condition. I use SelectRows but only get one result each time.

[lbendlin]

Flip the loops around (each and (v_period).  Also try it with the tables flipped around. Then try it with Table.AddKey for your join condition columns.

If you can, provide meaningful sample data.   That  will allow us to suggest the most performant approach.