Forum Discussion
how to dynamically replace values for multiple columns
Hello vc25,
You can dynamically replace values in all columns that start with "mt" by using a lookup mapping and Table.TransformColumns.
Step 1 – Create a mapping table
Create a small table with your replacements and load it into Power Query, for example call it StateMap:
Code | State
1 Georgia
2 Alabama
3 Texas
4 Florida
Step 2 – Use this M code in your main query
let
Source = YourPreviousStep,
// Get all columns that start with "mt"
MtColumns = List.Select(
Table.ColumnNames(Source),
each Text.StartsWith(_, "mt")
),
// Convert mapping table to record
MapRecord = Record.FromTable(
Table.TransformColumnTypes(StateMap, {{"Code", type text}})
),
// Replace values dynamically
Replaced = Table.TransformColumns(
Source,
List.Transform(
MtColumns,
(col) => {
col,
each Record.FieldOrDefault(MapRecord, Text.From(_), _),
type text
}
)
)
in
Replaced
Why this works
It automatically finds all columns named mt1, mt2, mt3, etc.
It replaces their numeric values using the mapping table.
It works even if the number of mt columns changes.
Everything is done in one single step, no hardcoding.
This is the standard scalable pattern for dynamic value replacement in Power Query.
does the statemap query need to be text or number data type? my "mt" columns are text type.
Hello vc25,
Good question. StateMap[Code] should be Text, since your mt columns are also Text.
In the solution, this line is the key: Record.FieldOrDefault(MapRecord, Text.From(_), _)
Because Text.From(_) is used, the lookup is done using text values. That’s why the mapping table is explicitly converted here:
MapRecord = Record.FromTable(
Table.TransformColumnTypes(StateMap, {{"Code", type text}})
)As long as both the mt column values and StateMap[Code] are text, the replacement will work correctly and scale as columns are added or removed.
Microsoft docs (reference):
Table.TransformColumns
https://learn.microsoft.com/powerquery-m/table-transformcolumnsRecord.FromTable
https://learn.microsoft.com/powerquery-m/record-fromtableRecord.FieldOrDefault
https://learn.microsoft.com/powerquery-m/record-fieldordefault
Hope that clarifies it
I keep getting this error.
mapping table called "fishnames"
Hello, Olufemi7 this approach is good but Record.FromTable expects a table with "Name" and "Value" columns. Test your solutions, gentlemen 😉
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-fromtableBecause 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.
Thank you very much, Olufemi7 , for such a descriptive answer. Could you please ask your AI assistant to compose a message so that it's (not yours) M code will look like a code (not as plain text) next time? This site has "Insert/Edit code sample" option just in case. Thank you and your AI friend for yours cooperation pertaining to this matter.
- Table.TransformColumns:
