Repleace values based on other table

[Ela7]

Hi Team,

I hope you can help me with my problem. I have searched for it but coldn't find perticular answer. 

I have a table with circa 30 positions where user name was changed from User Name 1 to User Name 2 and I have few reports where is needed to repleace the value from User Name 1 to User Name 2, when the User Name 1 is visible in other table in perticular column (Reviewer) and change it to User Name B but when the user name is else, then leave it as it is.

I don't want to add any other column and also write 30 positions to replease value but is needed for this to work also when to the first tabel will be added new position. 

User Name 1	User Name 2
Susanne Mars	Susanne Smith
Kathrin Kos	Kathrin Kosinska

Report Table

Reviewer

Susanne Mars

Kathrin Kos

Kathrin Kosinska

John Miller

Susanne Smith

Thomas Shelby

Many thanks in advance!

[Anonymous]

Hi @Ela7 ,

Another approach is that using 'Merge Queries' in Power Query.

Expand it.

Add a custom column.

Then you can remove the original name column and the User Name 2 column to keep the New Name column.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Ela7]

I am really sorry but I am stack:

[slorin]

= Table.ReplaceValue(
  #"TAT A1",
  each [Reviewer],
  each #"KN Previous Name List"{[User Name1=[Reviewer]]}[User Name2],
  (row, old, new) => try new otherwise old,
  {"Reviewer"} )

 Stéphane

[Ela7]

Hi Slorin,

thank you very match for you fast assistance.

Unfortunately I must be doing something wrong. I have two separate sources with those tables (names change -> KN Previous Name List and reviews -> TAT A1).

With the source should I refere on this query?

= Table.ReplaceValue(#"TAT A1",each [Reviewer],each #"KN Previous Name List" {[User Name1=[Reviewer]]} [User Name2],[User Name2],{"Reviewer"} )

Many thanks!

[slorin]

Hi,

= Table.ReplaceValue(
    Source, 
    each [Reviewer], 
    each User_Name{[User Name 1=[Reviewer]]}[User Name 2],
    (row, old, new) => try new otherwise old,
    {"Reviewer"}
)

Stéphane