Change date with today's date

[Creative_tree88]

Hi all - this si doing my head in.  I have a column called 'Event Date'.  When there's no event registered, our system uses 01/01/2099 in place of just a null.  I'm using dates to calculate performance but I need something in the cell to calculate with - if I used 01/01/2099 our metrics would look very odd!!

How can I replace all instances of 01/01/2099 with today's date??  I've tried numerous suggestions offered in other message boards, but none of them seem to work.  It's almost as if Power BI doesn't recognise the format or something??

Really appreciate your help.  Thanks all.

[danextian · Accepted solution]

Hi @Creative_tree88 ,

Below is a sample M query. What it does is  it replaces 1/1/2099  which is #date(2099,1,1) with the current date.  What you need to do is replace that date with any other date but in the formula bar, change the replace-to value to Date.From( DateTime.LocalNow())

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zdCxDcAgEEPRXagj5WwCyc2C2H8NKNL8TBDJzeu+PEYJnXsO1zKPTZOVvMhGdvImHzJBBckqsUqsEqvEKrFKrBKrxCqzyp+vXmaC/31yLg==", 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}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",#date(2099, 1, 1),Date.From( DateTime.LocalNow()),Replacer.ReplaceValue,{"Date"})
in
    #"Replaced Value"

[v-shex-msft]

Hi @Creative_tree88 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[danextian · Accepted solution]

Hi @Creative_tree88 ,

Below is a sample M query. What it does is  it replaces 1/1/2099  which is #date(2099,1,1) with the current date.  What you need to do is replace that date with any other date but in the formula bar, change the replace-to value to Date.From( DateTime.LocalNow())

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zdCxDcAgEEPRXagj5WwCyc2C2H8NKNL8TBDJzeu+PEYJnXsO1zKPTZOVvMhGdvImHzJBBckqsUqsEqvEKrFKrBKrxCqzyp+vXmaC/31yLg==", 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}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",#date(2099, 1, 1),Date.From( DateTime.LocalNow()),Replacer.ReplaceValue,{"Date"})
in
    #"Replaced Value"

[Creative_tree88]

Hi @danextian Many thanks.  I'm not sure how to translate that into an expression which works for my data?  My field is 'Event Date' and the table it's in is called "Performance Table".  Thank you.

[danextian]

Hi @Creative_tree88 ,

The M code is meant to be pasted to a blank query in the query editor. The resulting query will show you the steps.

Here is a video of what that code does.

[Syndicate_Admin]

Hi @Creative_tree88 If you do it with DAX instead of using M within Power Query, as you said @danextian, you can create a conditional column with DAX

[Creative_tree88]

@Syndicate_Admin - thank you.  That worked.  How can I change this formula for another column where there are just blank cells if no date.  I need it to show today's date if there is a blank cell.  Table is called 'Performance Table' and field is 'Date Reported'.

Many thanks!

[Joel_Cami · Accepted solution]

You need to use "ISBLANK" in the IF formula

Column = IF(ISBLANK('Performance Table'[Date Reported]),TODAY(),'Performance Table'[Date Reported])