Forum Discussion
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...
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"
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()?
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"