Forum Discussion

GeorgeBonanza's avatar
GeorgeBonanza
New Member
8 years ago
Solved

Replace Multiple Text Values With a Single Text Value

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.

  • RobertSlattery's avatar
    RobertSlattery
    8 years ago

    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"

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago
    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's avatar
      GeorgeBonanza
      New Member
      8 years ago

      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's avatar
        RobertSlattery
        Responsive Resident
        8 years ago

        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"