Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create a custom column that tracks if date is less than current day

Hi there,

I currently have a date column called "closing date", and I want to create a new custom column in Power Query that will either give "Expired" or "Active" depending if the "closing date" has surpassed current date or not. 

So for example, 

if closing date is less than todays date (example: DateTime.Date(DateTime.LocalNow())) then new column value = Expired

else the column value = Active

 

How would I be able to do this in Power Query? 

 

Need Help
Tutorial Requests
  • jbwtp's avatar
    jbwtp
    3 years ago

    Hi Anonymous,

     

    this is just an example to give you a taste of the possible solution. For your code you need to adopt the  Added Custom step. In your case if would look something like:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [closing date] < Date.From(DateTime.FixedLocalNow()) then "Expired" else "Active", type text)

     

    If you are using a standard Add Column button in the main menu, this is the code that you need to paste into the dialog box:

     

    if [closing date] < Date.From(DateTime.FixedLocalNow()) then "Expired" else "Active"

     

    Cheers,

    John

4 Replies

  • jbwtp's avatar
    jbwtp
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi Anonymous,

     

    Something like this?

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNQ3MlKK1YlWMkJiGyOxTZDYpiC2sbFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [Date] < Date.From(DateTime.FixedLocalNow()) then "Expired" else "Active", type text)
in
    #"Added Custom"

    Cheers,

    John

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi jbwtp 

      I'm a bit confused with this one, I've added it in, but it looks like it's just generated it's own date column. I want it to use my date column which is "closing date" and check if that's less than todays date. 

      • jbwtp's avatar
        jbwtp
        Icon for Memorable Member rankMemorable Member
        3 years ago

        Hi Anonymous,

         

        this is just an example to give you a taste of the possible solution. For your code you need to adopt the  Added Custom step. In your case if would look something like:

        #"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [closing date] < Date.From(DateTime.FixedLocalNow()) then "Expired" else "Active", type text)

         

        If you are using a standard Add Column button in the main menu, this is the code that you need to paste into the dialog box:

         

        if [closing date] < Date.From(DateTime.FixedLocalNow()) then "Expired" else "Active"

         

        Cheers,

        John