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 "Saturd...
  • 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"

     

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"