Power Query - filter & lookup values based on another table

[ASB21]

Hi all,

I have a table that I would like to add to a Power Query and then:

• filter one of the columns based on values that appear in a column in a separate table in a different worksheet
• add an additional column that gets populated using a lookup against the separate table

I've provided some simplified sample data below - in that example I'd like to filter Table1 to only show the Product Refs that appear in Table2, and then add a new column called Category into Table1 that does a lookup off Table2 to find the correct category for each Product Ref.

Book1
	A	B	C
1	Product Ref	Product Name	Stock Quantity
2	101	ABC	50
3	104	DEF	100
4	102	GHI	20
5	109	JKL	40
6	110	MNO	60
7	103	PQR	10
8	106	STU	20
9	108	VWX	30
10	105	YZA	50
11	107	BCD	60
Sheet1

Book1
	A	B
1	Product Ref	Category
2	101	C
3	102	B
4	103	A
5	104	D
6	105	B
Sheet2

Thank you in advance for any help you can offer!

 

[SpillerBD]

You can use a Merge where you keep all records from your Filter By list.

 

[ASB21]

Thank you but I'm quite new to Power Query - do you have any more detail on how I go about doing this please?

Many thanks

 

[Automatrix]

First, make a connection to Table2 and load it. Then a connection to Table1, where you choose Merge queries, and merge with Table2 (inner join; this will exclude rows only found in Table1) by highlighting Product Ref in both queries. Then Expand the new merged column (the data says "Table") using the icon to the right of the column name, keeping only the field "Category".

 

[alansidman]

If you are truly interested in expanding your PQ skills, then pickup this book Master Your Data with Power Query in Excel and Power BI

 

[ASB21]

First, make a connection to Table2 and load it. Then a connection to Table1, where you choose Merge queries, and merge with Table2 (inner join; this will exclude rows only found in Table1) by highlighting Product Ref in both queries. Then Expand the new merged column (the data says "Table") using the icon to the right of the column name, keeping only the field "Category".

That's amazing, worked perfectly, thank you so much!

 

[ASB21]

If you are truly interested in expanding your PQ skills, then pickup this book Master Your Data with Power Query in Excel and Power BI

Thanks for the recommendation Alan, I will definitely grab a copy - very keen to learn more on Power Query (I've been using Excel regularly for 20 years and can't believe I've only just discovered Power Query in the past 2 weeks!)

 

[Automatrix]

Thanks for the recommendation Alan, I will definitely grab a copy - very keen to learn more on Power Query (I've been using Excel regularly for 20 years and can't believe I've only just discovered Power Query in the past 2 weeks!)

It truly is Excel's best-kept secret. I am actually a bit shocked it isn't offered as a solution in more threads in this forum.