Approximate Match Lookup in Power Query: 2 Amazing Functions! Power BI or Excel. EMT 1865

Introduction

This tutorial will guide you through performing approximate match lookups in Power Query using M code. You will learn to create custom functions that can be applied in both Excel and Power BI. This approach is particularly useful for data analysts looking to transform and shape data effectively.

Step 1: Understanding Approximate Match Lookup

Approximate match lookups allow you to find the closest matching value in a dataset. This technique is essential when exact matches are not available, and you need to retrieve related data efficiently.

Key Points

• Approximate match lookups are beneficial for handling incomplete or non-exact data entries.
• They help in scenarios like finding the nearest price, rating, or any other metrics based on a reference table.

Step 2: Creating a Custom Column with Table.SelectRows Function

In this step, you will create a custom column using the Table.SelectRows function combined with the let expression.

Instructions

1. Open Power Query in Excel or Power BI.
2. Select the table where you want to add the custom column.
3. Go to the "Add Column" tab and select "Custom Column."
4. Use the following code snippet to define your custom column:

let
    Source = YourTableName,
    LookupValue = [YourLookupColumn],
    Result = Table.SelectRows(Source, each [YourMatchColumn] <= LookupValue)
in
    if Table.RowCount(Result) > 0 then Result{0}[YourReturnColumn] else null

• Replace YourTableName, YourLookupColumn, YourMatchColumn, and YourReturnColumn with your actual table and column names.
• This code will select rows where the match column is less than or equal to the lookup value, returning the first matching row.

Practical Tips

• Ensure your columns are appropriately formatted (numeric, text) to avoid errors.
• Test the custom column with various lookup values to validate its accuracy.

Step 3: Building a Reusable Function in the Advanced Editor

Next, you will create a reusable function using the Table.SelectRows function in the Advanced Editor.

Instructions

1. Open the Advanced Editor in Power Query.
2. Define your function with the following template:

let
    ApproximateMatch = (LookupValue as number) =>
    let
        Source = YourTableName,
        Result = Table.SelectRows(Source, each [YourMatchColumn] <= LookupValue)
    in
        if Table.RowCount(Result) > 0 then Result{0}[YourReturnColumn] else null
in
    ApproximateMatch

• Again, replace the placeholder names with your actual data.

Common Pitfalls

• Ensure your function is named and referenced correctly.
• Double-check that your parameters match the expected data types.

Step 4: Utilizing List.Accumulate Function

The final method involves creating a reusable function using the List.Accumulate function for more complex scenarios.

Instructions

1. In the Advanced Editor, you can create the function as follows:

let
    ApproximateMatchAccumulate = (LookupValue as number) =>
    let
        Source = YourTableName,
        Sorted = List.Sort(Table.Column(Source, "YourMatchColumn")),
        Result = List.Accumulate(Sorted, null, (state, current) =>
            if current <= LookupValue then current else state)
    in
        Result
in
    ApproximateMatchAccumulate

• This function sorts the match column and accumulates the closest value that meets the criteria.

Real-World Applications

• This method is particularly useful for financial data analysis, where you may need to find the closest budget figures or performance metrics.

Conclusion

In this tutorial, you've learned how to perform approximate match lookups in Power Query using M code through various methods. By creating custom columns and reusable functions, you can enhance your data analysis capabilities in Excel and Power BI.

Next Steps

• Experiment with different datasets to apply these techniques.
• Consider reading the M code book by Mike Girvin for deeper insights into Power Query functionalities.

With these skills, you will be better equipped to handle complex data analysis tasks efficiently!