Custom function with Table.TransformRows

Hi Mic1979, Can you please try the below code:

(data, replacements, condition_column, condition_values as list, columns_list as list) =>
let
// Generates a record with replacements
repl = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),
transformations = List.Transform(columns_list, (name) => {name, (x) => Record.FieldOrDefault(repl, x, x)}),

// Replace values in rows
replace = Table.TransformRows(
data,
(x) => if List.Contains(condition_values, Record.Field(x, condition_column))
then Record.TransformFields(x, transformations)
else x
),

result = Table.FromRecords(replace)
in
result

Hi Mic1979, another solution without custom function:

Before

After

v1

let
    Source = Web.BrowserContents("https://docs.google.com/file/d/1N2JsMNBgqoMbvRK5p5hyrNl8i2KokzNZ/edit?filetype=msexcel"),
    SourceHelper = Html.Table(Source, {{"Column1", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(1)"}, {"Column2", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(2)"}, {"Column3", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(3)"}, {"Column4", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(4)"}, {"Column5", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(5)"}, {"Column6", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(6)"}}, [RowSelector="TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR"]),
    data_table = Table.PromoteHeaders(SourceHelper[[Column1], [Column2], [Column3]]),
    replacements = Table.SelectRows(Table.PromoteHeaders(SourceHelper[[Column5], [Column6]]), each [Old_Material] <> ""),
    R = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),
    StepBack = data_table,
    Transformed = Table.TransformRows(StepBack, each
        if List.Contains({"Butt Welding ASME BPE", "Clamp ASME BPE"}, [Port_Type_1_2])
        then List.Accumulate(Record.FieldNames(_), _, (s,c)=> Record.TransformFields(s, {{c, (y)=> Record.FieldOrDefault(R, Record.Field(_, c), y) }}))
        else _ ),
    ToTable = Table.FromRecords(Transformed, Value.Type(StepBack))
in
    ToTable

v2

let
    Source = Web.BrowserContents("https://docs.google.com/file/d/1N2JsMNBgqoMbvRK5p5hyrNl8i2KokzNZ/edit?filetype=msexcel"),
    SourceHelper = Html.Table(Source, {{"Column1", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(1)"}, {"Column2", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(2)"}, {"Column3", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(3)"}, {"Column4", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(4)"}, {"Column5", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(5)"}, {"Column6", "TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR > :nth-child(6)"}}, [RowSelector="TABLE.ndfHFb-c4YZDc-hDEnYe-Df1ZY-bN97Pc > * > TR"]),
    data_table = Table.PromoteHeaders(SourceHelper[[Column1], [Column2], [Column3]]),
    replacements = Table.SelectRows(Table.PromoteHeaders(SourceHelper[[Column5], [Column6]]), each [Old_Material] <> ""),
    R = Function.Invoke(Record.FromList, List.Reverse(Table.ToColumns(replacements))),
    StepBack = data_table,
    Replaced = Table.ReplaceValue(StepBack, 
        each List.Contains({"Butt Welding ASME BPE", "Clamp ASME BPE"}, [Port_Type_1_2]),
        null,
        (x,y,z)=> if y then Record.FieldOrDefault(R, x, x) else x,
        Table.ColumnNames(StepBack) )
in
    Replaced

Function - you can cut out F function and paste it into a new query to make it usable for whole document

let
    FileLink = Web.Contents("https://docs.google.com/uc?export=download&id=1yJGxTqikuKoGMwZEshory8ue6vmumcU-"),
    ExcelWorkbook = Excel.Workbook(FileLink),
    Source = Table.PromoteHeaders(Table.Skip(ExcelWorkbook{[Item="invoke",Kind="Sheet"]}[Data])),
    F = (tbl as table, DO_col as text, DCP_col as text, DO_cond as list, DCP_cond as list)=>
        Table.FromRecords(Table.TransformRows(tbl, each
                if List.Contains(DO_cond, Record.Field(_, DO_col)) and List.Contains(DCP_cond, Record.Field(_, DCP_col))
                then Record.TransformFields(_, {{DO_col, (x)=> "NPN / PNP"}, {DCP_col, (x)=> "NOT APPLICABLE"}})
                else _ ), Value.Type(Table.FirstN(Source, 0)) ),
    InvokedF = F(Source, "Digital_Output", "Digital_Communication_Protocol", {"NO OUTPUT", "condition2", "condition3..."}, {"IO Link", "condition2", "condition3..."})
in
    InvokedF