Need help with dynamic exception table for replacing values.

Hi Anonymous 

I have attached an example of how you could handle this. I used Power Query in Excel since your screenshot appeared to be Excel, but this can be moved to Power BI if needed.

I have assumed that

1. The first matching row (if any) from the Exception table will be used.
2. In the Exception table, we never have Category = null.

My method is:

1. First create queries from Source and Exception tables.
2. Create a function fnReplacementCategory which is based on the Exception table. This function:
   1. Takes a record as input. The record will in practice be an individual row of the Source table.
   2. Finds the first row of Exception (if any) where the Column/Value pair exists in the record.
   3. If there is a matching row of Exception, return the value of Category from that row, otherwise null.
3. Create an Output query which
   1. References the Source table.
   2. Applies the fnReplacementCategory function to each row, and if it returns a non-null value then replace Category by that value.

Below is the M code for fnReplacementCategory and Output:

// ============= fnReplacementCategory =====================================
let
  ExceptionList = Table.ToRecords(Table.Buffer(Exception)),
  ReplacementCategoryFunction = (InputRecord as record) =>
    let
      MatchingExceptions = List.Select(
        ExceptionList,
        each Record.FieldOrDefault(InputRecord, _[Column]) = _[Value]
      ),
      FirstMatch = List.First(MatchingExceptions),
      Category = FirstMatch[Category]?
    in
      Category
in
  ReplacementCategoryFunction

// ============= Output ===================================================
let
  Source = Source,
  #"Replace Category" = Table.ReplaceValue(
    Source,
    each [Category],
    each fnReplacementCategory(_),
    Replacer.ReplaceText,
    {"Category"}
  )
in
  #"Replace Category"

Hopefully this is of some help, and can be tweaked if needed.

Regards