Replace Multiple Text Values With a Single Text Value

[GeorgeBonanza]

I am working in Power Query in Excel and I have a table with a column that contains the days of the week by name, i.e. "Sunday", "Monday", "Tuesday", ... etc.

I want to replace the values "Saturday" and "Sunday" with "Weekend" and the leave the other days with their day name and I want to have the results in that same column.  

I can do this in two different steps

Table.ReplaceText(InputTable, "Saturday",  "Weekend", Replacer.ReplaceText, {"Day"})

Table.ReplaceText(InputTable, "Sunday",  "Weekend", Replacer.ReplaceText, {"Day"})

but I would rather do it in one step if possible.

Something that would be equivalent to

if [Day] in ("Saturday", "Sunday") then "Weekend" else [Day]

Thanks in advance.

[Greg_Deckler]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7NS0msVIrVATITS0qLYBzffLh4SGlqMYwdnpqSh+CFZJQWwTluRZlgZiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Day = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type text}}),
    #"Replaced Value" = Table.ReplaceValue(Table.ReplaceValue(#"Changed Type","Sunday","Weekend",Replacer.ReplaceText,{"Day"}),"Saturday","Weekend",Replacer.ReplaceText,{"Day"})
in
    #"Replaced Value"

[GeorgeBonanza]

Ok, I see you nested two Table.ReplaceValue().  Suppose I wanted to do the opposite, keep the name if it is Saturday or Sunday and replace Monday through Friday with "Weekday".

Is there an easier way than nesting five Table.ReplaceValue()?

[RobertSlattery · Accepted solution]

There are a few different ways but this is probably best for your case...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7NS0msVIrVATITS0qLYBzffLh4SGlqMYwdnpqSh+CFZJQWwTluRZlgZiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Day = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type text}}),
    weekendDays = {"Sunday", "Saturday"},
    #"Replaced Value" = Table.TransformColumns(#"Changed Type",
        {"Day", each if List.Contains(weekendDays, _) then "Weekend" else _ }
    )
in
    #"Replaced Value"

The second argument in Table.TransformColumns is a list containing the target column and an iterator function with the signature 

( _ as any) as any => ...

the each key word is syntactic sugar (short-hand) for this.

You could generalise it a bit like this...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7NS0msVIrVATITS0qLYBzffLh4SGlqMYwdnpqSh+CFZJQWwTluRZlgZiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Day = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type text}}),
    replacer = [targets = {"Sunday", "Saturday"}, replacement = "Weekend"],
    #"Replaced Value" = Table.TransformColumns(#"Changed Type",
        {"Day", each if List.Contains(replacer[targets], _) then replacer[replacement] else _ }
    )
in
    #"Replaced Value"

And just to give some insight into the language, these options give the same result...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7NS0msVIrVATITS0qLYBzffLh4SGlqMYwdnpqSh+CFZJQWwTluRZlgZiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Day = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type text}}),
    weekendDays = {"Sunday", "Saturday"},
    #"Replaced Value" = 
        Table.ReplaceValue(#"Changed Type",
            each _[Day], each if List.Contains(weekendDays, _[Day]) then "Weekend" else _[Day],
            Replacer.ReplaceText ,
            {"Day"}
        )
in
    #"Replaced Value"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7NS0msVIrVATITS0qLYBzffLh4SGlqMYwdnpqSh+CFZJQWwTluRZlgZiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Day = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type text}}),
    weekendDays = {"Sunday", "Saturday"},
    #"Replaced Value" = 
        Table.ReplaceValue(#"Changed Type",
            null, null,
            (_, old, new) => if List.Contains(weekendDays, _) then "Weekend" else _,
            {"Day"}
        )
in
    #"Replaced Value"