Forum Discussion

Ela7's avatar
Ela7
New Member
2 years ago

Repleace values based on other table

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 1User Name 2
Susanne MarsSusanne Smith
Kathrin KosKathrin Kosinska

 

Report Table

 

Reviewer
Susanne Mars
Kathrin Kos
Kathrin Kosinska
John Miller
Susanne Smith
Thomas Shelby

 


Many thanks in advance!

Need Help

5 Replies

  • slorin's avatar
    slorin
    Super User
    2 years ago

    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 

  • Ela7's avatar
    Ela7
    New Member
    2 years ago

    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's avatar
    slorin
    Super User
    2 years ago
    = 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.