how to dynamically replace values for multiple columns

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,

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:

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.

Hi vc25 this is the next part of the same question we worked on ?

Thankyou, SundarRaj, ronrsnfld, Olufemi7, AlienSx and techies for your responses.

Hi vc25,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solutions provided by ronrsnfld and techies to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.