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

[NumeroENAP]

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!

[Jimmy801]

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]

@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 · Accepted solution]

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

[NumeroENAP]

Well, I found my problem on https://docs.microsoft.com/fr-fr/power-bi/service-admin-troubleshooting-power-bi-personal-gateway

It was indeed due to the data source.

I checked my datas, and there was a single data that wasn't right... 

[Jimmy801]

Hello

well... your syntax posted in the first post will never work, independent from your data source

Bye

Jimmy

[NumeroENAP]

Urgh, I thought it was the data that was the problem, but no...

I tried to compare my query with the one you provided, but it still doesn't work. 

Mine : 

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

Yours @Jimmy801 

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

I replaced the "_" with my [column name]. 

Otherwise, my tablename is "SOURCE". I don't know if it was supposed to go somewhere in my synthax. 

Thanks

[TheDataMustFlow]

Don't replace the "_", it represents the row value of [Date Created].

If you insert the code exactly as @Jimmy801 posted after the #"LastLine", then it should work.

If it doesn't, can you post the whole query?

[NumeroENAP]

Well, I was being stuborn because I didn't knew that I could use the symbol "_" in a query (oups). 

It was my bad. Thank you @TheDataMustFlow for this comprehensive answer. 

[Jimmy801]

Hello @NumeroENAP 

this is not my code. Check it out.. mine should work

JImmy