VLOOKUP functionality in Power Query

[zz_zz]

I am new to Power Query and I am having difficulties recreating the functionality of VLOOKUP from Excel.

I have two tables in Power Query. One is product sales and the other is product cost. I need to bring the cost to the sales table. The issue is that costs change over time so each product comes with a cost and a particular month. I merged columns (Month and SKU) so both tables have identical columns and now I just need to figure out how to merge them. I tried merging but then I am getting a lot more rows than my original sales table contain (not sure why though).

 

[AliGW]

Probably using the wrong join type. I loaded table 1 to connection only, then loaded table 2 and merged with table 1 on product and month (right outer join).

Be aware that there is a trailing space after Jan in B6.

 

[Ken Puls]

Hi zz_zz,

What kind of VLOOKUP are you trying to use? Exact or Approximate? It looks like you always have each product for each month in the pricing table, so I'm going to assume it's Exact.

The secret is to base your join on two columns (Product-Month) instead of one. To do that, use the Left Outer join, but when making the merge hold down your CTRL key to select multiple columns. (Just make sure you go with a consistent order.) One issue I see here is that product C's "Jan" entry is "Jan " (with a space), which means you won't get an exact match, as your data is dirty. You will need to fix that, either by trimming the columns prior to merging them, or cleaning up the source data.