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.

[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 

[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,