Range Lookup for Tiers (Approximate Match) in Power Query M

Matches a value to the correct tier in a lookup table based on a minimum threshold, like Excel's approximate VLOOKUP.

Comparing & LookupsAdvancedrange lookuptiersapproximate match

Power Query M code

let
    Source = YourTable,
    AddedTier = Table.AddColumn(Source, "Tier", (row) =>
        Table.Last(Table.SelectRows(TierTable, each [MinValue] <= row[Value]))[TierName]
    )
in
    AddedTier

Notes

TierTable must be sorted ascending by MinValue; Table.Last picks the highest tier threshold that's still at or below the row's value.

Requirements

A tier lookup table with MinValue and TierName columns, sorted ascending by MinValue.

How to use this snippet

In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Range Lookup for Tiers (Approximate Match).