replace values in columns that start the same using if statements

[vc25]

hello, i want to replace values in columns that start the same using an if statement. I have multiplec columns that start with "cdp" and each with numbers (data type text) and corressponding values to replace them with. like 1 = ab, 2 = ac etc. is it possible to do it with if statements?

[v-hjannapu]

Hi @vc25,

I would also take a moment to thank @MarkLaf  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Regards,
Community Support Team.

[v-hjannapu]

Hi @vc25,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Community Support Team.

[MarkLaf · Accepted solution]

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)

[Mauro89]

Hi @vc25,

is there a specific requirement or constrain to use Power Query for that?
You could also think of using TMDL view to change the corresponding column names. Check out the docs for more information: Use Tabular Model Definition Language (TMDL) view in Power BI Desktop - Power BI | Microsoft Learn

Or maybe this article can help you as well: How I Bulk Change Column Names in Power BI | by AnalystHub | Lucid Plexus | Medium

Best regards!

PS: If you find this post helpful consider leaving kudos or mark it as solution

[vc25]

yeah I need to do it in power query to create my data visuals. I want to change the cell values, not the column names. each cell value has a corresponding value like "1" should be replaced by "ab" in columns that start with cdp.

[Mauro89]

Ok got it. You can achieve this by using the UI feature "Replace values" in the transform tab of Power Query. There you can enter the "value to find" and also the "replace with" value. Repeat for all your statements and you should be good to go. 

[vc25]

yes. However, I wanted a way to replace values in columns that start with cdp. like I have columns labels cdp1, cdp2, cdp3, and that numbre of columns can vary so I would want it to dynamically replace value in columns

[Mauro89]

Ok so having also a condition on "cdp". Then maybe this query might work for you:
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] = "cdp1" then "cdpab" else if [Column1] = "cdp2" then "cdpcd" else [Column1], type text).

But iam not aware of a function the takes the "cdp" as condition first and the replaces the values.

Best regards!