Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Query Editor: IF Date < then empty

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])

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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"})

4 Replies

  • Angith_Nair's avatar
    Angith_Nair
    Continued Contributor
    5 years ago

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

      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 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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"})