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", "Name"}, {"State", "Value"}}

)

// Replace values dynamically

Replaced =

Table.TransformColumns(

Source,

List.Transform(

MtColumns,

(col) => {

col,

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

type text

}

)

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.

Forum Discussion

URGENT! DATES

Urgent question about Policy update

Need Urgent Help on SamePeriodLastYear

urgent help needed

Urgent Help - Power Query - Data Transformation

URGENT: Single Cell Date Replace