Table.ReplaceValue + Text.removeRange for mutiple columns

Hi informer ,

Yes, it is possible to write a single script to apply the transformation to multiple columns dynamically using Table.TransformColumns in Power Query (M language). Instead of repeating Table.ReplaceValue for each column, you can define a list of target columns and apply the same transformation logic to all of them. The following script accomplishes this by using List.Transform to iterate over each column and applying Text.RemoveRange to remove the acronym:

let
    Source = #"Add col Nom",
    ColumnsToTransform = {"Name", "Volume(24h)", "Circulation Supply"},
    TransformData = Table.TransformColumns(
        Source,
        List.Transform(ColumnsToTransform, 
            each {_, 
                each Text.RemoveRange(_, Text.Length(_) - Text.Length([Acronyme]), Text.Length([Acronyme])),
                type text
            }
        )
    )
in
    TransformData

This method ensures that all specified columns are processed in one step, making the script more efficient and easier to maintain. If additional columns require the same transformation, they can simply be added to the ColumnsToTransform list without modifying the core logic.

Best regards,

Now that you have provided a usable data sample, it is apparent you can remove the Acronymes in a single step.

Enter the code below as a step after the last step in your query that shows the table sample you provided. Replace #"Previous Step" with the name of the actual previous step.

You can see from the screenshots it has removed the Acronyme from each of those three columns

#"Remove Acronyme" = Table.ReplaceValue(
        #"Previous Step",
        each [Acronyme],
        null,
        (x,y,z) as text=> if Text.EndsWith(x,y) 
                            then Text.Trim(
                                Text.ReplaceRange(
                                    x,Text.Length(x)-Text.Length(y),Text.Length(y),"" )) 
                            else x,
        {"Nom","Volume","Circulating Supply"})

#"Previous Step"

#"Remove Acronyme"