Lookup a Value in a different table and return a value from another column

Power Query M Code Showcase
1

Hi,

I have 2 tables. The results table has two categories.

The combination of these two categories gives me my lookup key.

My problem is that the lookup table allows wild cards…

So to determine the price for ‘A Blue’, I must first look for 'A Blue, and if that is not there , I must look for for ‘A *’.

In the real scenario there are actually 4 permutations. I could run multiple merges, but that seems a bit inelegant.

I am sure I have done this before, I just can’t think how.

Thank you

image
image723×176 8.76 KB

2

@kellysolutions,

One way to accomplish this that doesn’t seem terribly inefficient or inelegant would be to merge the Results table and the Lookup table using the Primary Name and Secondary Category as keys. If no exact match is found, you’d use the Primary Name with the wildcard entry (*) in the Secondary Category from the Lookup table.

I started with these tables:

image

image

and this query:

Final Table query 
let
    Results = results_table,
    Lookup = lookup_table ,   

    // Exact Match
    ExactMatch = Table.NestedJoin(Results, {"Primary Name", "Secondary Category"}, Lookup, {"Primary Category", "Secondary Category"}, "ExactMatch", JoinKind.LeftOuter),
    ExactMatchExpanded = Table.ExpandTableColumn(ExactMatch, "ExactMatch", {"Price"}, {"ExactPrice"}),

    // Fallback Match
    WildcardMatch = Table.NestedJoin(ExactMatchExpanded, {"Primary Name"}, Table.SelectRows(Lookup, each [Secondary Category] = "*"), {"Primary Category"}, "WildcardMatch", JoinKind.LeftOuter),
    WildcardMatchExpanded = Table.ExpandTableColumn(WildcardMatch, "WildcardMatch", {"Price"}, {"WildcardPrice"}),

    // Final Price
    FinalPrice = Table.AddColumn(WildcardMatchExpanded, "Price", each if [ExactPrice] <> null then [ExactPrice] else [WildcardPrice]),

    // Clean Up 
    FinalResult = Table.SelectColumns(FinalPrice, {"Primary Name", "Secondary Category", "Price"})
in
    FinalResult

which results in:

image

I hope