cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LY18
Helper I
Helper I

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

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_EQUIPMENTMEASURE_DATECustom
EQTB091/1/2020 12:07:03 AMOK
EQTB091/1/2020 12:07:05 AMOK
EQTB091/1/2020 12:07:06 AMOK
EQTB091/1/2020 12:07:07 AMOK
EQTB031/1/2020 1:48:46 AMOK
EQTB041/1/2020 8:39:52 AMOK
EQTB041/1/2020 12:04:45 PMOK
EQTB041/1/2020 12:04:46 PMOK
EQTB041/1/2020 12:04:50 PMOK
EQTB041/1/2020 12:04:54 PMOK
EQTB021/1/2020 12:54:00 PMOK
EQTB061/1/2020 8:30:56 PMOK
EQTB101/1/2020 9:08:43 PMOK
EQTB051/2/2020 1:09:48 AMOK
EQTB091/2/2020 5:06:24 AMOK
EQTB071/2/2020 12:59:20 PMOK
EQTB061/2/2020 3:02:10 PMOK
EQTB041/3/2020 7:57:02 AMOK
EQTB021/3/2020 2:02:26 PMOK
EQTB031/3/2020 3:11:11 PMOOC

 

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.BATCHS/N.BATCH SUBdate_insertCLUSTERdate_extraction
98986A9927641/2/2020EQTB021/4/2020
98986A9927741/4/2020EQTB021/6/2020
9X957B9X26751/6/2020EQTB021/9/2020
98986A9927931/9/2020EQTB021/11/2020
98986A9927911/11/2020EQTB021/12/2020
99989B9X06101/12/2020EQTB021/15/2020
99989B9X06591/15/2020EQTB021/17/2020
99989B9X06501/17/2020EQTB021/20/2020
99991B9X11821/20/2020EQTB021/23/2020
99991B9X11511/23/2020EQTB021/25/2020
99991B9X11451/25/2020EQTB021/26/2020
99991B9X11751/26/2020EQTB021/29/2020
9X957B9X26861/29/2020EQTB021/30/2020
9X957B9X26611/30/2020EQTB02null
98986A9927541/1/2020EQTB031/4/2020
9X957B9X26671/4/2020EQTB031/6/2020
9Y952A9Y2321/6/2020EQTB031/9/2020
9Y952A9Y23381/9/2020EQTB031/11/2020
99989B9X06521/11/2020EQTB031/12/2020
99989B9X0671/12/2020EQTB031/15/2020
9Z959A9Z17771/15/2020EQTB031/17/2020
99989B9X06491/17/2020EQTB031/19/2020
99991B9X11621/19/2020EQTB031/22/2020
99991B9X11811/22/2020EQTB031/25/2020
99991B9X11431/25/2020EQTB031/26/2020
99991B9X11761/26/2020EQTB031/28/2020
99991B9X11331/28/2020EQTB031/29/2020
9X958A9X26571/29/2020EQTB031/31/2020
9X957B9X26571/31/2020EQTB03null

 

 

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 

EQTB031/3/2020 3:11:11 PMOOC98986A9927 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

1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
Community Support

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.

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.

View solution in original post

2 REPLIES 2
v-xuding-msft
Community Support
Community Support

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.

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.

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors