Forum Discussion
Power Query - Get data from another table if condition is met
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!
4 Replies
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
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
