How to do a real VLOOKUP in Power BI (or expand just certain rows after a merge)

[ImkeF]

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:

and a transaction table with multiple entries 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:

 

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):

Now I tweak the code in the formula bar like so:

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-po...

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:

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 😉 .

Did you found that useful? Go and check out my blog, where I posted a more tips & tricks: https://www.thebiccountant.com/

Enjoy and stay queryious 😉