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.
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!
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 |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |