Need help with dynamic exception table for replacing values.

[Anonymous]

Hi all, 

I would like to write a PQ-script where a source table and an exception table are combined into a corrected table where the category is corrected based on a condition specified in the exception table.  

The  first column of the exception table specifies which column in the source table needs to be used to run the conditional statement through.

The second is the matching value. If the condition is true, than the category in the source table should be corrected. 

See example hereunder, anyone has an idea? 

It was formerly set up as a hardcoded conditional column but I would like to set it up as a table so it stays visible in a workbook and other people can add rows to the exception table and update the corrected table.  

Thanks for any help 

[AlienSx]

Hi, @Anonymous 

let
    source = your_source_table,
    exception = your_exception_table,
    e_lst = List.Buffer(Table.ToRows(exception)),
    txform = 
        Table.FromRecords(
            Table.TransformRows(
                source, 
                (r) =>
                    Record.TransformFields(
                        r,
                        {"Category", 
                        (x) => 
                            [poz = List.PositionOf(e_lst, r, Occurrence.First, (a, b) => Record.Field(b, a{0}) = a{1}),
                            out = if poz = -1 then x else e_lst{poz}{2}][out]}
                    )
            )
        )
in
    txform

[ThxAlot]

Simple enough, one line of code.

[OwenAuger]

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