Getting Column from another table by applying filter

[Harvey85]

Hello everyone, I have the following query:

I have table A and table B. Between tables A and B, I have a common field as a key, called Camp_ID.

Table A is my main data table. From table B, I need to filter the rows where the Category field starts with 'Spring*' and get the End_Date column from that table and bring it to table A.

How can I filter the data in table B and bring the End_Date column to table A? I would not need to have table B in the model, it is only for obtaining that field. Thank you!!

[SivaMani]

@Harvey85 , I suggest that you use Power Query to merge Table A and Table B, and then add the required columns from Table B using the Expand Columns function. This way, you will not need to include Table B in the model, as it is only necessary for obtaining the desired field.

https://learn.microsoft.com/en-us/power-query/merge-queries-overview

[amitchandak · Accepted solution]

@Harvey85 , new column in table A

Maxx(filter(TableB, Tablea[Camp_id] = tableB[campId] && containsstring(TableB[categoty], "Spring") ), TableB[Date])

[Harvey85]

Thanks @amitchandak for your kind reply!

One more question, if I had a list of IDs to filter instead of Category=Spring... how could I do?

Let's say I'd need to filter ID_Category 1, 5, 7... To modify the formula?

Thanks in advance!!