Forum Discussion

Plump_Lil_Monk's avatar
Plump_Lil_Monk
Frequent Visitor
2 years ago
Solved

Custom Column: Conditional Value Return

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                                         YearAny                           Current Not current
2025Current +1
2024Current
2024Current
2025Current +1
2025Current +1
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Plump_Lil_Monk's avatar
    Plump_Lil_Monk
    Frequent Visitor
    2 years ago

    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's avatar
    Anonymous
    Not applicable
    2 years ago

    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.