Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Need help with dynamic exception table for replacing values.

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 ex...
Need Help
Show and Tell
AlienSx's avatar
AlienSx
Super User
2 years ago

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