Reply
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

How to convert "Present" value text to Current Year?
02-04-2023
11:48 PM
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.
1 ACCEPTED SOLUTION
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-05-2023
06:20 AM

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
Microsoft Employee
2 REPLIES 2
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-05-2023
06:20 AM

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
Microsoft Employee
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-06-2023
04:38 AM

Thank you very much!

Helpful resources
Top Kudoed Authors (Last Month)
User | Count |
---|---|
11 | |
11 | |
9 | |
7 | |
6 |