none
Replace value in column based upon string match in another column RRS feed

  • Question


  • I have a table which has data as below

    Employee Name Location    Manager Name
    Anne                 London Elizabeth
    Peter                 New York Mike
    Paul                  Lisbon Dorothy
    Marek               New York Mike

    In the above table I want to search all employees in location that contains string "York" and for those which found, replace value in column 'Manager Name' with "Tim"

    Request help to build a M query how this can be achieved. Help is appreciated
    Thursday, August 1, 2019 3:19 PM

Answers

  • Hi Rohit,

    This code should work:

    replace = Table.ReplaceValue(YourTable, each Text.Contains([Location], "York"), "Tim", (a,b,c)=>if b then c else a, {"Manager Name"})

    If you want to search not only "York", but also "york", "YORK" etc. just add 3rd argument of Text.Contains:

    Text.Contains([Location], "York", Comparer.OrdinalIgnoreCase)
    Thursday, August 1, 2019 3:53 PM

All replies

  • Hi Rohit,

    This code should work:

    replace = Table.ReplaceValue(YourTable, each Text.Contains([Location], "York"), "Tim", (a,b,c)=>if b then c else a, {"Manager Name"})

    If you want to search not only "York", but also "york", "YORK" etc. just add 3rd argument of Text.Contains:

    Text.Contains([Location], "York", Comparer.OrdinalIgnoreCase)
    Thursday, August 1, 2019 3:53 PM
  • Thanks Aleksei. That worked perfect.
    Friday, August 2, 2019 2:57 AM
  • Just an additional question since I am a newbie to M Query

    How should the code be written if I want to replace  Manager name if Location contains "York" AND Name contains "Peter"?

    Appreciate if you can share any good resources on getting upto speed with M query syntax and tutorials

    Friday, August 2, 2019 3:07 AM
  • Appreciate if you can share any good resources on getting upto speed with M query syntax and tutorials
    You can start here: Learning resources
    • Edited by anthony34 Friday, August 2, 2019 7:18 AM
    Friday, August 2, 2019 7:17 AM
  • How should the code be written if I want to replace  Manager name if Location contains "York" AND Name contains "Peter"?

    Like this:

    replace = Table.ReplaceValue(YourTable, each Text.Contains([Location], "York")
                                                                     and Text.Contains([Employee Name], "Peter"),
                                                                     "Tim",
                                                                     (a,b,c)=>if b then c else a, {"Manager Name"})
    Friday, August 2, 2019 8:34 AM