Forum Discussion

NumeroENAP's avatar
NumeroENAP
Helper III
6 years ago
Solved

Getting errors with a column transformation (future dates to today's date)

Hi,

 

I want to replace future dates in my column by today's date, but i'm only getting errors. 

 

My query : 

= Table.TransformColumns(#"LastLine", {"Date created", each if [Date created] > (DateTime.FixedLocalNow()) then Date.From(DateTime.FixedLocalNow()) else [Date created], type date})

 

My data = a simple date column (type date)

 

The message showed : 

Expression.Error : Sorry... We coulnd't apply access to field type Date.
Details :
Value=2020-01-24
Key=Date created

 

I tried to change my column to format TimeDate, but it returns the same error message with «type DateTime» at the end...

 

Thanks!

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    here the correct version of your syntax

     

    = Table.TransformColumns(#"LastLine", {"Date created", each if _ > (DateTime.FixedLocalNow()) then Date.From(DateTime.FixedLocalNow()) else _, type date})



    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

9 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello NumeroENAP 

     

    this is a double post

    link 

    use the syntax that is specified there to solve your problem

     

    = Table.TransformColumns
  (
      #"Column", 
      {
         {
            "Date created", 
            each if _ > Date.From(DateTime.FixedLocalNow()) then Date.From(DateTime.FixedLocalNow()) else _,
            type date
         }
      }
   )

     

    Fix your syntax according to this

     

    Jimmy

    • NumeroENAP's avatar
      NumeroENAP
      Helper III
      6 years ago

      Jimmy801  When I saw that my query was bad today, I tried to fix it according to what you wrote me, but I'm still stucked with my problem. 

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        6 years ago

        here the correct version of your syntax

         

        = Table.TransformColumns(#"LastLine", {"Date created", each if _ > (DateTime.FixedLocalNow()) then Date.From(DateTime.FixedLocalNow()) else _, type date})



        If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
        Kudoes are nice too

        Have fun

        Jimmy