Creating Prioritized/Exception-Based Mapping in PQ

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
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

Value to Map
Level 1
Level 2
Level 3
Country
A
Pears
B
Granny Smith
C
Fruit
USA
D
Fruit
E
Vegetables
Canada
F
Vegetables

<tbody>
</tbody>


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

Mapped Value
Level 1
Level 2
Level 3
Country
Explanation
D
Fruit
Apples
Gala
Mexico
Rule 4 which is the most generic fruit rule (doesn't meet any of the other more specific fruit rules)
B
Fruit
Apples
Granny Smith
Canada
Rule 2 because level 3 is granny smith
D
Fruit
Blueberries
N/A
Canada
Rule 4 (same as row 1)
E
Vegetables
Broccoli
N/A
Canada
Rule 5 because it is a vegetable and country is Canada
F
Vegetables
Broccoli
N/A
USA
Rule 6 because it is the most generic vegetable rule

<tbody>
</tbody>


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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top