Power Query - Get data from another table if condition is met

[Anonymous]

Hi all,

I need your help to get data from table 2 if some conditions are met there, and this in power query. I have a ref that I can found in both table, and I need to retrieve the "to return value" if Date from table 1 is within Start and End date of table 2. There are overlap in date for the same ref in Table 2, in such case I just need the first value that match the critera. This is what I have:

Table 1

Ref	Date
1	05.01.2022
2	03.03.2023
1	01.03.2023
1	18.02.2022
3	13.08.2022
...	...

Table 2

Ref	Start Date	End Date	Value to return
1	01.01.2000	31.12.2002	abc
1	01.01.2022	30.06.2022	def
1	01.01.2022	30.11.2022	azt
2	01.01.2023	31.03.2023	uir

The result I am looking for

Ref	Date	Returned value
1	05.01.2022	def
2	03.03.2023	uir
1	01.04.2023	null
1	18.02.2022	def
3	13.08.2022	azt
...	...	...

Thanks in advance for your help!

[HealthyBob]

Is IT because you See an Error or is it not selectable? Do you have the right linkage? Or is it just a Table as visual? Then you have to get the Data in the Backend Tables. Otherwise try to geht an measure feom your Table with calcualte(values( which you can select in the If Statement 

[Anonymous]

Tables are well connected, however IF statement cannot compare a value from a cell to a table, I guess.

[HealthyBob]

Hi,

maybe you can try Something Like that 

= If( And("Date Table 1" > " start Date table 2", "Date Table 1"< "end Date Table2), Firstnonblank(value to Return, value to Return), "Else condition"))

Im new with Firstnonblank, but it worked for me to Type in the returning value twice.

Best

[Anonymous]

That would be great, but you cannot even select any field from table 2 with a simple if statement