Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago
Solved

Replace values for certain items in another column

Hello,

 

I want to replace some of the values of one column when it matches values from another query (table). I know I can use merge queries and conditional columns, but I do not want to use replace values.

E.g in the table below I need to replace the Department from "-" to "Sales" but not for every value in the Name column. Instead, I want to be able to provide the table with the names concerned (e.g just for Leo and Ken).

 

Any help is highly appreciated 😊

Name       Department

JoshIT
NathanServices
Leo_
Margaret_
Ken_
DenisIT
FloraServices
SandraServices
  • slorin's avatar
    slorin
    2 years ago

    Hi

    With Exception = {"Ken", "Leo"}

    = Table.ReplaceValue(
       Source,
       each [Name],
       each [Department],
       (row,name,dep)=> if List.Contains(Exception,name) and dep="_" then "Sales" else dep,
       {"Department"}
    )

    Stéphane 

2 Replies

  • slorin's avatar
    slorin
    Super User
    2 years ago

    Hi

    With Exception = {"Ken", "Leo"}

    = Table.ReplaceValue(
       Source,
       each [Name],
       each [Department],
       (row,name,dep)=> if List.Contains(Exception,name) and dep="_" then "Sales" else dep,
       {"Department"}
    )

    Stéphane