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 😉

[affan]

Wonder share. I had been struggling with this problem, thanks for resolving 🙂

[Anonymous]

• Hello!.. I'm merging 2 query's invoice table andand payments table, my first problem is that when I do the left merge ( putting the payment into the invoices table) it's not putting the values where on the correct match so let say I have 5 invoices but only 1 of those should have a payment the merging is putting some other payments in the incorrect invoice.
• Then I tried the merge the other way around, in the payments table using right merge with the invoices table and it actually puts in in the correct invoice..wow it matches! One I'll think!!! . I go to my visuals I do my 2 tables, one table contains the account number and it should tell me I have 1 invoice for that acc, and it does! But then if I drill to see those invoices, expecting it gives me the payment in the correct invoice number (like I have it on my original table) well it doesn't!!! It gives me the payment in another invoice :@. I have the connections made between INVOICE numbers in the two tables...
• You think you can give a hand? I've tried everything!!! And nothing works 😞
• Thanks!!