Replace column value based on another column

Question

#"Added Custom2" = Table.AddColumn(#"Added Custom1", "pm plus", 
each pmplus[Qualifies for]{List.PositionOf(pmplus[Combined Criteria],[combined criteria])}),

#"Replace NA" = 
        Table.ReplaceValue(#"Added Custom2", each[pm plus],
        each if Text.Contains(Text.From([Dept ID]),"33320") then "PM" 
        else if Text.Contains(Text.From([Dept ID]),"33330") then "PM" 
        else Text.From([pm plus]),Replacer.ReplaceValue, {"Dept ID"})

I added a new column named "pm plus". Now I want to replace values in said column depending on the value in another column named "Dept ID". The transformation gave no errors but nothing was replaced. If ID is not found, keep original value.

findwindow · Accepted Answer · 2025-09-05 20:48:05Z

0

    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "pm plus", each pmplus[Qualifies for]{List.PositionOf(pmplus[Combined Criteria],[combined criteria])}),

    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom2", {{"pm plus", "z"}}),
    #"Replaced PM" = Table.ReplaceValue(#"Replaced Errors", each[pm plus],
        each if [Dept ID]="33320" then "PM" 
        else [pm plus],Replacer.ReplaceText, {"pm plus"})

The column had errors so had to replace errors first.

answered Sep 5 at 20:48

findwindow

3,1831 gold badge15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Ron Rosenfeld Sep 6 at 12:03

If the errors originate in your data itself, and you cannot correct the data, then Table.ReplaceErrorValues is appropriate. However, if the errors originate in the Power Query code, you may be able to combine things into just a single Table.ReplaceValue function using a custom replacer function.

findwindow Sep 8 at 13:22

I am adding lookup columns with powerquery which may result in an error. Not sure if that qualifies.

Ron Rosenfeld Sep 8 at 15:49

Probably does but I would have to see the data and m-code to suggest a solution

Ron Rosenfeld Sep 8 at 16:52

I recommend removing errors when you generate your column, not after.

Ron Rosenfeld Sep 10 at 23:10

There was a comment, since deleted, indicating that you were doing something similar to xlookup, which prompted my related comment. Since you are not, handling the error at the time of column creation might be more efficient. But, as always, perfect is the enemy of good enough, and if your code is working as you want, you might not want to spend any more time tweaking it. Just something to keep in mind for the future.

Ron Rosenfeld Sep 8 at 15:50

Or you may be able to remove the errors in your custom column code

findwindow Sep 8 at 16:32

That's what that step is doing. Replacing errors with "z".

findwindow Sep 8 at 17:00

Won't let me reply to you last comment so doing it here. How do I remove errors as I go? It's effectively a xlookup.

Ron Rosenfeld Sep 8 at 21:51

If you are mimicking Excel's XLOOKUP, then set something in the not found so it won't return an error. Depending on the rest of your code, possibly null.

Ron Rosenfeld Sep 9 at 13:16

Looking at your code posted above, what is the error being returned? You could check that the proper values are present in your data, or you possibly handle the error using try...otherwise (not my favorite as it may mask other errors).

findwindow Sep 10 at 21:18

I don't really have time to look at this now but there is no xlookup equivalent in m that I am aware of so there's no not found parameter to set.

Collectives™ on Stack Overflow

Replace column value based on another column

1 Answer 1

11 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Related