Merging info with a date in a first table and a range of date in the second one

[LY18]

Hi,

I'm fighting with a probleme and i couldn't find a way to make it work

I've tried to look deeply in this article https://powerpivotpro.com/2019/02/powerquerymagic-conditional-joins-using-table-selectrows/

i'm almost sure this "magic function" is the solution to my problem .... but could not make it work

here are some details (maybe you will be able to help me 😉 )

in a first table i have a list of measure done on some equipments with a date of measurement 

PROCESS_EQUIPMENT	MEASURE_DATE	Custom
EQTB09	1/1/2020 12:07:03 AM	OK
EQTB09	1/1/2020 12:07:05 AM	OK
EQTB09	1/1/2020 12:07:06 AM	OK
EQTB09	1/1/2020 12:07:07 AM	OK
EQTB03	1/1/2020 1:48:46 AM	OK
EQTB04	1/1/2020 8:39:52 AM	OK
EQTB04	1/1/2020 12:04:45 PM	OK
EQTB04	1/1/2020 12:04:46 PM	OK
EQTB04	1/1/2020 12:04:50 PM	OK
EQTB04	1/1/2020 12:04:54 PM	OK
EQTB02	1/1/2020 12:54:00 PM	OK
EQTB06	1/1/2020 8:30:56 PM	OK
EQTB10	1/1/2020 9:08:43 PM	OK
EQTB05	1/2/2020 1:09:48 AM	OK
EQTB09	1/2/2020 5:06:24 AM	OK
EQTB07	1/2/2020 12:59:20 PM	OK
EQTB06	1/2/2020 3:02:10 PM	OK
EQTB04	1/3/2020 7:57:02 AM	OK
EQTB02	1/3/2020 2:02:26 PM	OK
EQTB03	1/3/2020 3:11:11 PM	OOC

On a second table i got a batch install info , with the batch name and sub name , the date of install and the date of extract

S/N.BATCH	S/N.BATCH SUB	date_insert	CLUSTER	date_extraction
98986A9927	64	1/2/2020	EQTB02	1/4/2020
98986A9927	74	1/4/2020	EQTB02	1/6/2020
9X957B9X26	75	1/6/2020	EQTB02	1/9/2020
98986A9927	93	1/9/2020	EQTB02	1/11/2020
98986A9927	91	1/11/2020	EQTB02	1/12/2020
99989B9X06	10	1/12/2020	EQTB02	1/15/2020
99989B9X06	59	1/15/2020	EQTB02	1/17/2020
99989B9X06	50	1/17/2020	EQTB02	1/20/2020
99991B9X11	82	1/20/2020	EQTB02	1/23/2020
99991B9X11	51	1/23/2020	EQTB02	1/25/2020
99991B9X11	45	1/25/2020	EQTB02	1/26/2020
99991B9X11	75	1/26/2020	EQTB02	1/29/2020
9X957B9X26	86	1/29/2020	EQTB02	1/30/2020
9X957B9X26	61	1/30/2020	EQTB02	null
98986A9927	54	1/1/2020	EQTB03	1/4/2020
9X957B9X26	67	1/4/2020	EQTB03	1/6/2020
9Y952A9Y23	2	1/6/2020	EQTB03	1/9/2020
9Y952A9Y23	38	1/9/2020	EQTB03	1/11/2020
99989B9X06	52	1/11/2020	EQTB03	1/12/2020
99989B9X06	7	1/12/2020	EQTB03	1/15/2020
9Z959A9Z17	77	1/15/2020	EQTB03	1/17/2020
99989B9X06	49	1/17/2020	EQTB03	1/19/2020
99991B9X11	62	1/19/2020	EQTB03	1/22/2020
99991B9X11	81	1/22/2020	EQTB03	1/25/2020
99991B9X11	43	1/25/2020	EQTB03	1/26/2020
99991B9X11	76	1/26/2020	EQTB03	1/28/2020
99991B9X11	33	1/28/2020	EQTB03	1/29/2020
9X958A9X26	57	1/29/2020	EQTB03	1/31/2020
9X957B9X26	57	1/31/2020	EQTB03	null

what io want as a result would be for each measure of the table 1 , what was the batch installed at that time in the equipment

ex 

EQTB03

1/3/2020 3:11:11 PM

OOC

98986A9927

54

i tried to do a add custom colum in the TABLE MEASURE and add this formula

=Table.SelectRows( #"TABLE BATCH", (Magic) => ([PROCESS_EQUIPMENT]= Magic[CLUSTER] and [MEASURE_DATE]>= Magic[date_insert] and [MEASURE_DATE]<= Magic[date_extraction]) )

but it didn't work ;-( ... when i try to expend i got the message (No columns were found.)

if someone can give me a hint it would be great 

Thanks in advance

[v-xuding-msft · Accepted solution]

Hi @LY18 ,

It might be caused that the data type of "MEASURE_DATE" is Datetime, but the columns of "date_insert" and "date_extraction" are Date type. You could convert the datetime values to date values by the function of Date.From. Please try the code below.

= let date = Date.From([MEASURE_DATE]) in Table.SelectRows(#"Table 2", (Magic) => ([PROCESS_EQUIPMENT]= Magic[CLUSTER] and date>= Magic[date_insert] and date<= Magic[date_extraction]) )

For more details, please see the attachment.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LY18]

Yes you were right 

the issue was that i was comparing date with date_time ..... 

many thanks for your help 😉

PS : i love this "magic function" 😉