how to dynamically replace values for multiple columns

[vc25]

In power query, I previously split columns by a delimiter, so some of the columns in my query start the same "mt" with variations like "mt1" "mt2" mt3" etc. Each "mt" column is datatype text and has a number (1,2,3,4, etc..) in the cell. I want to replace the number with a value such as "1" replaced with Georgia, "2" replaced with Alabama, and so on. The amount of columns split can vary so I need to replace the values dynamically based on how many columns were split by the delimiter. I would also like to be able to replace the values all in one step, so I don't need to apply the code multiple times if possible.

How can I code this?

[ronrsnfld]

You can do this using the Table.ReplaceValue function with a custom replace function.

First as others have recommended,  create a Mapping Table.

For example:

You can then use this code:

let

//Replace next line with your actual data source of the splitted table
    Source = Table,

//create list of columns to evaluate
    Cols = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "mt")),

//Lookup the Key
//Assumes Mapping Table is a two column table the columns named "State" and "Key"
    Mapping = List.Buffer(Table.ToRecords(#"Mapping Table")),

//Execute the replacements
    #"Replace with State" = Table.ReplaceValue(
        Source,
        null,
        Mapping,
        (x,y,z)=>if x=null then null else List.Select(z, each [Key]=Number.From(x)){0}[State] ,
        Cols)
in
    #"Replace with State"

With an initial "split" table that looks like:

After running the code:

[Olufemi7]

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.

[AlienSx]

Hello, @Olufemi7 this approach is good but Record.FromTable expects a table with "Name" and "Value" columns. Test your solutions, gentlemen 😉

[Olufemi7]

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.

[AlienSx]

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. 

[vc25]

does the statemap query need to be text or number data type? my "mt" columns are text type. 

[Olufemi7]

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-transformcolumns

• Record.FromTable
  https://learn.microsoft.com/powerquery-m/record-fromtable

• Record.FieldOrDefault
  https://learn.microsoft.com/powerquery-m/record-fieldordefault

Hope that clarifies it 

[vc25]

I keep getting this error. 

mapping table called "fishnames"

[SundarRaj]

Hi @vc25

Using a demo structure, I have tried to replicate the solution to the query you've asked for.

This is considering you have a "Reference" Table matching the Numbers popping up to it's respective countries that you wish to replace in the column names.

This solution is predominantly about finding the number of delimiters present and using that count, frame the number of columns with the desired name.

I'll leave the excel file below for your reference. Please let me know in case of any queries or if this wasn't what you were looking for. Thanks!

Regards,

[vc25]

I can not see your steps or code