Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
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"
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
98 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |