Variables from Multiple columns lookup to insert new column in Power Query or Power Pivot

Question

Apologies for the layout from below but it is a sample with 4 columns.

I need to insert the target from Table below in a new column, based on the Product (Quite a long list) together with the Priority (ranking 1 to 4 but only gave 2 below) which is also dependent on office or After Hours.

Can someone please help with a formula to auto - populate this either in Power Pivot or Power Query or both so I can use either.

Product Priority Hours Target A 1 OH One A 1 AH Two A 2 OH Three A 2 AH Four B 1 OH One B 1 AH Two B 2 OH Three B 2 AH Four

My raw data contains columns for Product,Priority and Hours. I also have a seperate table as indicated above with all products priorities and hours in columns 1, 2 and 3.

Thanks in Advance Keith

Answer 1

I would use the Power Query Merge feature for this. It can connect 2 Queries together, using matching columns. Queries can start from an Excel table.

There's a good description and walkthrough for Merge on the PQ support site: https://support.office.com/en-us/article/Merge-queries-Power-Query-fd157620-5470-4c0f-b132-7ca2616d17f9