Hi, I need help...
How to convert "Present" value text to Current Year using Custom Column?
Table Example:
StartYear | EndYear | ConvertedEndYear |
2022 | Present | 2023 |
2020 | 2021 | 2021 |
Thank you!
Solved! Go to Solution.
Do a Replace Values on the column and replace "Present" with any text (e.g., "A"). Then update the step expression in the formula bar, replacing "A" with Text.From(Date.Year(DateTime.LocalNow())). Below is an example you can put in a blank query to see how it works.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDKAUIZKsTrRSk4wjo5SQFFqcWpeiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, StartYear = _t, EndYear = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"StartYear", Int64.Type}, {"EndYear", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","Present",Text.From(Date.Year(DateTime.LocalNow())),Replacer.ReplaceText,{"EndYear"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"EndYear", Int64.Type}})
in
#"Changed Type1"
Pat
Do a Replace Values on the column and replace "Present" with any text (e.g., "A"). Then update the step expression in the formula bar, replacing "A" with Text.From(Date.Year(DateTime.LocalNow())). Below is an example you can put in a blank query to see how it works.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDKAUIZKsTrRSk4wjo5SQFFqcWpeiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, StartYear = _t, EndYear = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"StartYear", Int64.Type}, {"EndYear", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","Present",Text.From(Date.Year(DateTime.LocalNow())),Replacer.ReplaceText,{"EndYear"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"EndYear", Int64.Type}})
in
#"Changed Type1"
Pat
Thank you very much!