Conditional column with time range within power querry

[Pete230]

Hi togehter;

I would like to achive the following: I have an table(table 1) with IDs and time ranges, e.g.:

ID	From	To
ABC	10.10.2023	15.10.2023
DEF	05.09.2023	12.12.2023

I have a nother table (table 2) which looks like this:

ID	Date
ABC	01.01.2023
ABC	02.01.2023
..	..

Here(within table 2) I would like to add an additinal column which writes an X in it when the time range of table 1 matches the date and ID column in table 2; like this:

ID	Date	Match
ABC	08.10.2023	-
ABC	09.10.2023
ABC	10.10.2023	X
ABC	11.10.2023	X
ABC	12.10.2023	X
ABC	13.10.2023	X
ABC	14.10.2023	X
ABC	15.10.2023	X
ABC	16.10.2023	-
ABC	17.10.2023	-
…	…

Does anyone know how to achive that?

Thank you and cheers, Pete!

[v-cgao-msft · Accepted solution]

Hi @Pete230 ,

Please try like:

if Table.IsEmpty(Table.SelectRows(Table1,(x)=>x[ID]=[ID] and [Date]>=x[From] and [Date]<=x[To])) then null else "X"

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Pete230]

Wow, perfect! That works, thanks a lot!
I have just another problem occuring now; the table2 of mine is 10.000.000 rows long; now the querry takes forever; which was kind of fast before; because of this new implemented "x" feature; do you know any way how to make this more efficient?

Thank you!
Cheers, Pete

p.s. whats a good source to learn M as you used it above in a structured way?