Custom Column: Conditional Value Return

[Plump_Lil_Monk]

Hi, I have a problem that I am trying to figure out.

I need to create a custom column that enters a value based on a high-low condition in another column.

I have a year column with 2024 and 2025 in it, next year these values will change to 2025 and 2026.

I need some M which will automatically identify the lowest value (current year) in my year column and then output it as 'Current Year' and Identify the highest value (next year) and output it, in the custom column, as 'Current Year +1'. 

Any help would be appreciated. 

How I would need it to look.

123                                         Year	Any                           Current Not current
2025	Current +1
2024	Current
2024	Current
2025	Current +1
2025	Current +1

[Plump_Lil_Monk]

Thanks for the help that worked.

But after playing around I found a slightly different way still using your method:

Instead of:

currentYear = Date.Year(DateTime.LocalNow()),
    AddedCustom = if [Year] = currentYear then "Current" else "Current + " & Text.From([Year] - currentYear)

I used:

    AddedCustom = if [Year] = Date.Year(DateTime.LocalNow()) then "Current" else "Current +"

[Anonymous · Accepted solution]

Hi @Plump_Lil_Monk ,

Please try this M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlGK1UFlmGIwsErFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
  let 
    currentYear = Date.Year(DateTime.LocalNow()),
    AddedCustom = if [Year] = currentYear then "Current" else "Current + " & Text.From([Year] - currentYear)
  in 
    AddedCustom)
in
    #"Added Custom"

Put all of it into the advanced editor, and the final output is below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.