VLOOKUP True in Power Query

A new method to replicate VLOOKUP (TRUE) in Power Query
By Oz du Soleil 3 months agoNo Comments

About the Author of this Pattern

The following VLOOKUP True pattern was created by a close friend of both Miguel Escobar and Ken Puls. His name is Oz du Soleil and he’s a Data Ninja who enjoys fighting the good fight against bad data using Power Query, not to mention that he always ends up on top. Oz is a fantastic blogger, YouTuber and is also a Microsoft Most Valuable Professional (MVP) since the year 2015. Be sure to check his YouTube channel, website and follow him on all of his social media accounts.

Why Replicate Excel’s VLOOKUP Function?

As much as some people try to avoid VLOOKUP(), it is an incredibly useful function for Excel pros. Those who love it will certainly want to replicate its functionality in Power Query at some point. The thing is, however, depending on which version of VLOOKUP() you need, such as VLOOUP True, it can be quite tricky to implement.

Determining an Exact Match

In truth, we don’t need to do anything special to emulate VLOOKUP’s Exact Match, as this functionality can be replicated by simply merging two tables together.

Making an Approximate Match

Replicating VLOOKUP()’s approximate match is a totally different case than the exact match scenario. It requires some logic to emulate these steps, as we’re not trying to match records against each other, we’re actually trying to find the closest record to our request without going over.

Download the Workbook

Explaining VLOOKUP True

It’s always bothered me that VLOOKUP True is considered an approximate match. See … 64 and 65 could be considered approximate until you need VLOOKUP True to make 65 the line between passing and failing. Suddenly, 64 and 2 have more in common, and 65 is in a whole different world that includes 66, 83 and 99.

This point became relevant recently when I needed to use Power Query to replicate the real function of VLOOKUP True: assigning records to a category or tier.

Up until now, it’s been easy to replicate VLOOKUP-True functionality: just stack up a few conditions, Load To, then move on with life.

Example. To assign each of these amounts to the categories of Small, Medium or Large it takes just two conditions.

Condition-Stacking… No Way!

Recently, I took on a project where that wouldn’t work because of the way Power Query hardcodes values and does other things that we’ve come to understand are shameful and immoral.

There are two main reasons why this new project was problematic

  1. Replicating VLOOKUP True with a stack of conditions will hard-code the categories. And unless you’re ready to dig around in the query, you can’t quickly
    1. add or remove a tier or
    2. change the size of the tiers
  2. If you have a lot of tiers to create, that will be a lot of time just stacking up conditions.

In this example, we need to figure out the right discount amount and make it easy for a user to, for example: remove the 30-pound tier and make the 20 – 50 range $2.65.

So, how can this be made simpler?

My Solution

After a few hours of internet searches for a solution….

I remembered to think TIERS, not about approximate matches. Thus, it would be fantastic if:

  1. I could get the discounts in exactly the right positions between the transactions thus, distinguishing the tier demarcations.
  2. I had a way to identify and delete the tier demarcations when everything is done.

Oh lord! I could suddenly envision the path to glory!

  • Append
  • Sort
  • Fill down
  • Filter

Here’s the video of my solution:

Step 1: Add Column of Flags

My first step was to make a column of flags as an easy visual check during the development process, and an obvious record to delete if everything worked.

Step 2: Append Queries

With the queries created, the Amount columns needed to be exact so that the queries could be appended. Thus, change the Discounts query’s Amounts (lbs) column to Amounts. Then append the 2 tables:

Step 3: Sort by LOOKUP Column

Things were looking great. But here’s the missing step in the video.

After posting it, Szilvia Juhasz noticed that the sort had a problem. The 30 in row 19 is equal to the tier demarcation 30 in row 20, but shows above the demarcation. WRONG! The demarcation needs to be listed first.

This is solved with a Sort by Transaction to assure that null comes before any transaction where the demarcation is equal to an actual value.

(*NOTE: one thing to think about is tiers that have no values. What happens? Fortunately, that’s not a problem here. The Xs in rows 18 and 19 don’t make problems for us just because there are no orders that are >= 20 and < 30.)

Step 4: Fill Down Column

The Hard Part is Over

We’ve got this thing whooped now!

In the next screenshot, you can see that the flag isn’t totally necessary. To get rid of the demarcations, we can filter out either, the x in the Flag column or the null in the Transaction column.

Step 5: Filter out the Placeholder value (X)


Filter out the Xs

Remove the Flag column then Load To…

Brothers and sisters, we’re done!!

But let’s try it out.

Further Testing

Does it work? With the following changes to the Discounts what happens when we refresh?
Result!
Thanks to Ken Puls and Miguel Escobar for allowing me to share this guest article with you. I appreciate their early efforts and passion for Power Query. So, it’s a real honor to be here with you. Now, go forth and help keep this world’s data clean!

Power Query Patterns
Power Query Book

this work was shared 0 times
 200
About

 Oz du Soleil

  (1 articles)

Oz du Soleil is a Microsoft Excel MVP and known for his YouTube channel, Excel on Fire: https://www.youtube.com/c/OzduSoleilDATA There, you can see swords, fire, data-cleansing techniques and Oz's unique style of teaching Excel. Oz lives in Portland, Oregon and is co-author of Guerilla Data Analysis, 2nd Ed. with Mr. Excel, Bill Jelen.

x
})(jQuery);