replace values in columns that start the same using if statements

Here is one way to do it. Define the value replacements you want in a record. Then use Table.ReplaceValues with 1) a custom replacer function that does the replacement lookup and 2) use Table.ColumnNames + List.Select to perform it only on your cdp columns.

 

Sample (all "numbers" are text)

Column1	cdpColumn2	Column3	cdpColumn4	cdpColumn5	Column6	Column7
1	1	3	3	3	5	5
2	2	1	4	2	1	2
1	1	2	4	1	2	4
3	2	5	3	2	4	2
4	3	3	5	1	5	1

 

Advanced editor

let
    Source = Sample,
    Replacements = [1="ab",2="ac",3="ad",4="ae",5="af"],
    ReplaceCdps = Table.ReplaceValue( 
        Source, null, null, 
        // custom replacer: ignore oldValue and newValue (why we can just have them be null)
        // simply lookup replacement using value
        (val,old,new) as text => Record.FieldOrDefault( Replacements, val, "! missing replacement" ), 
        // select col names with cdp start for above replacer
        List.Select( Table.ColumnNames( Source ), each Text.StartsWith(_,"cdp") ) 
    )
in
    ReplaceCdps

 

Output

 

Edit: switched in Record.FieldOrDefault for customer replacer as that won't error if replacement is missing, but can still flag as an issue (as I do above - alternatively, can replace with original val, null, or whatever)