Query Editor: IF Date < then empty

[Anonymous]

Hi,

I never work with Query Editor much, so I'm not sure how to do this.

I have a date field from a very old system, which doesn't know blank/null values. Meaning that if a date field is empty in the database its 01-01-1753.

I want to have every date before 1900 to be blank.  I tried something like below, but that doesn't seem to work.

= Table.AddColumn(#"Removed Other Columns", "Custom", each if [ARCHIEF_DATUM] <= Date.AddYears(DateTime.Date(DateTime.LocalNow()), -100) then 0 else [ARCHIEF_DATUM])

[Angith_Nair]

Hi @Anonymous ,

Instead of making dates before 1900 blank , just filter the dates is after or equal to 01/01/1900 (assuming that dates before 1900 are not in use) in Power Query level. This will also decrease your refresh time.

[Anonymous · Accepted solution]

I actually found a way because you made me think of it.

= Table.ReplaceValue(#"Replaced Value",#datetime(1753, 1, 1, 0, 0, 0),null,Replacer.ReplaceValue,{"ARCHIEF_DATUM"})

[Angith_Nair]

You can also refer to this  link.

[Anonymous]

Hi, @Angith_Nair, thanks for the input. But I can't do that. I need the data that it's connected to. The frontend software of the database does not show 1753 but shows the date as not filled. I want to replicate that in my table by making the date blank 🙂