how to dynamically replace values for multiple columns

Hello AlienSx,

Thank you for the comments  to clarify and close the loop.

The overall approach is correct: dynamically identifying all mt* columns and replacing their values via a lookup using Table.TransformColumns. The only required refinement is how Record.FromTable is applied.

Per the official Microsoft documentation, Record.FromTable expects a table with columns named Name and Value. It does not work directly on arbitrary column names like Code and State:

Record.FromTable(table as table) as record
Returns a record from a table of records containing field names and value names {[Name = name, Value = value]}.
— Microsoft Docs
https://learn.microsoft.com/powerquery-m/record-fromtable

Because of that contract, the mapping table needs a small reshape (rename) before converting it to a record.

Also, since the mt columns are text, the mapping key (StateMap[Code]) should be Text as well. This aligns with the lookup expression using Text.From(_).

Corrected and complete M code

let

    Source = YourPreviousStep,

    // Get all columns that start with "mt"

    MtColumns =

        List.Select(

            Table.ColumnNames(Source),

            each Text.StartsWith(_, "mt")

        ),

    // Convert mapping table to Name / Value record

    MapRecord =

        Record.FromTable(

            Table.RenameColumns(

                Table.TransformColumnTypes(

                    StateMap,

                    {{"Code", type text}}

                ),

                {{"Code", "Name"}, {"State", "Value"}}

            )

        ),

    // Replace values dynamically

    Replaced =

        Table.TransformColumns(

            Source,

            List.Transform(

                MtColumns,

                (col) => {

                    col,

                    each Record.FieldOrDefault(MapRecord, Text.From(_), _),

                    type text

                }

            )

        )

in

    Replaced

Supporting references:

• Table.TransformColumns:
  https://learn.microsoft.com/powerquery-m/table-transformcolumns
• Record.FieldOrDefault:
  https://learn.microsoft.com/powerquery-m/record-fieldordefault

With the mapping table reshaped to Name / Value, this remains a standard, scalable pattern for dynamic value replacement in Power Query  no hard-coding, and resilient to changing numbers of mtcolumns.