When you merge tables with distinct keys in Power Query you will get the same result than the VLOOKUP-function in Excel returns (if this is new to you, check out this article for example: https://www.myonlinetraininghub.com/excel-power-query-vlookup) .
But how to retrieve only the result of the first row, if the lookup-table has multiple rows with the same key?
Background
Say you have a dimension table for products:
Product table with one row per Product
and a transaction table with multiple entries per product:
Transactions table with multiple rows per Product
The task is to create 2 additional columns in your dimension table. One to show the first price at which the product has been sold and the other one the corresponding first date:
Select only first rows per Product
If you merge the transactions to the dimension table and expand it, you will end up with as much rows in the dimension table as there are in transaction table.
Problem
So how to retrieve only the elements of the first row of the matching tables? I’ll show you 2 different methods:
Solution 1 – Tweak the aggregation code
This is very quick to implement if you just want to return one or a few columns from the lookup-table: In the dialogue where you usually expand the columns, check “Aggregate” instead and click on one of the suggested aggregations for each column that I’m interested in (I simply ignore for a moment that these are not the aggregations that I actually need):
Choose one (false) aggregate per column
Now I tweak the code in the formula bar like so:
Tweaking Code for real VLOOKUP
Replacing the default aggregations by what I need (in red: List.First) and adjusting the column names directly in that command (in green: just to save one manual step later).
To avoid long query durations on large tables, you can transform the key column of the dimension table to a real key column, like Chris Webb has described here: https://blog.crossjoin.co.uk/2018/03/16/improving-the-performance-of-aggregation-after-a-merge-in-power-bi-and-excel-power-query-gettransform/
Solution 2 – Add a column that selects the whole desired row
If you want to retrieve many more columns from your lookup table, the method above can become a bit tedious. Then it might be easier to add a column, that grabs the whole first row instead: Table.First would do that job:
Add a column to retrieve the full first (or last) row
Then simply expand out all fields that you need.
Bonus
You can use many different selection operations with this technique: So List.Last or Table.Last would give you the latest prices for example. This would actually be a more realistic use case here … and is the reason why I didn’t solve the original problem with just removing duplicates 😉 .
Enjoy and stay queryious 😉