Creating Prioritized/Exception-Based Mapping in PQ

[cr731]

I have a requirement to create a mapping table that is based on 4 columns of my source data where the mapping can occur at 1 or a combination of multiple of those 4 columns. More specific rules (those specifying multiple columns or using the columns representing lower hierarchy levels) will be listed in the mapping table first, and would need to take priority over generic rules at the bottom.

As an example, my mapping is based on 4 columns, three of which are part of a hierarchy and another is a separate dimension.

Level 1 represents a type of food (i.e. fruit/vegetable)
Level 2 represents a sub-category of that food (i.e. apples, pears, broccoli)
Level 3 represents the lowest level of the category (i.e. Granny Smith apple)
Country (unrelated to the food hierarchy but another column relevant to the mapping)

My mapping table looks like this

[TABLE="class: grid, width: 735"]
<tbody>[TR]
[TD]Value to Map
[/TD]
[TD]Level 1
[/TD]
[TD]Level 2
[/TD]
[TD]Level 3
[/TD]
[TD]Country
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD][/TD]
[TD]Pears
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Granny Smith
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]Fruit
[/TD]
[TD][/TD]
[TD][/TD]
[TD]USA
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]Fruit
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]Vegetables
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Canada
[/TD]
[/TR]
[TR]
[TD]F
[/TD]
[TD]Vegetables
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So this is saying that if level 2 is pears, the corresponding value is A - regardless of what is in any other column. This rule must be looked at first and takes priority over any subsequent rule.

Here is a sample of data with the values that should be mapped

[TABLE="class: grid, width: 735"]
<tbody>[TR]
[TD]Mapped Value
[/TD]
[TD]Level 1
[/TD]
[TD]Level 2
[/TD]
[TD]Level 3
[/TD]
[TD]Country
[/TD]
[TD]Explanation
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]Fruit
[/TD]
[TD]Apples
[/TD]
[TD]Gala
[/TD]
[TD]Mexico
[/TD]
[TD]Rule 4 which is the most generic fruit rule (doesn't meet any of the other more specific fruit rules)
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]Fruit
[/TD]
[TD]Apples
[/TD]
[TD]Granny Smith
[/TD]
[TD]Canada
[/TD]
[TD]Rule 2 because level 3 is granny smith
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]Fruit
[/TD]
[TD]Blueberries
[/TD]
[TD]N/A
[/TD]
[TD]Canada
[/TD]
[TD]Rule 4 (same as row 1)
[/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]Vegetables
[/TD]
[TD]Broccoli
[/TD]
[TD]N/A
[/TD]
[TD]Canada
[/TD]
[TD]Rule 5 because it is a vegetable and country is Canada
[/TD]
[/TR]
[TR]
[TD]F
[/TD]
[TD]Vegetables
[/TD]
[TD]Broccoli
[/TD]
[TD]N/A
[/TD]
[TD]USA
[/TD]
[TD]Rule 6 because it is the most generic vegetable rule
[/TD]
[/TR]
</tbody>[/TABLE]


I have struggled to come up with a Power Query solution to this but I know it must be possible. The solution would need to perform on a data set around 100,000 rows with about 25 rows in the mapping table.

Any insight is appreciated.

Thanks