How to filter rows in a table based on values in a

Question

I have two tables in power query.

Price table

Date       Company  Price
01/01/2000  A        10
01/02/2000  A        12
01/03/2000  A        15
01/01/2000  B        15
01/02/2000  B        85
01/03/2000  B        98 

Size table

 Date      Company  Size
01/06/2000  A        10
01/06/2001  A        12
01/06/2002  A        15
01/06/2000  B        15
01/06/2001  B        85
01/06/2002  B        98 

In Price table, I want only to have companies which are in size table. In other words, If company C is not in the size table, I do not need that company data points in the price table. Here no need to consider the date.

Answer 1

In Power Query you can use the Merge Queries function to achieve that. (In the Home --> Combine section of the ribbon.

Select the Join Kind to determine which rows to keep.

In your example, create a query from the 2nd table and apply the following steps:

• Remove the date and the size column
• Remove duplicates

Afterwards you can join the first table with the newly created query and do a inner join. (Only keep matching entries)