Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chalmers
New Member

Select first row metting condition

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.

1 REPLY 1
lbendlin
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors