Replacing Bad Dates

[AgingRapidly]

Trying to get start and end dates to use to create a calendar table.

I have data in which there are several bad dates. Ideally, they would be corrected in the source, but I'm getting push back on that request.



An example of a bad date is 04/06/74 12:00:00 AM highlighted above. I added a Year column to extract the year to find out exactly what year it is. Turns out to be 2074 when it should be 2000. I know it should be 2000, because I also extracted the year from other date columns and they showed 2000. I can't use the other date columns, because they have even more bad date issues.

I tried replacing value 04/06/74 12:00:00 AM with 04/06/00 12:00:00 AM. That didn't work.

I tried replacing 04/06/74 12:00:00 AM with 04/06/2000 12:00:00 AM. That didn't work.

I tried the dates without the 0s before month and day. That didn't work.

Any suggestions on how I can correct this handful of bad dates in my raw data query?

Thank you
Melinda

 

[pinarello]

Take this:

    let
        Source          = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        Change_Type     = Table.TransformColumnTypes(Source,{{"InvoiceDate", type datetime}}),
        Correct_InvDate = Table.ReplaceValue(Change_Type,
                          each [InvoiceDate],
                           each if Date.Year([InvoiceDate]) = 2074
                           then DateTime.From(
                                #datetime(2000,
                                          Date.Month([InvoiceDate]),
                                          Date.Day([InvoiceDate]),
                                          Time.Hour([InvoiceDate]),
                                          Time.Minute([InvoiceDate]),
                                          Time.Second([InvoiceDate])))
                           else [InvoiceDate],
                          Replacer.ReplaceValue,{"InvoiceDate"})
    in
        Correct_InvDate

 

[AgingRapidly]

Pinarello,
Thank you for the response.

I happened to be on a Skillwave course Q&A session yesterday afternoon with Ken and he gave me a less complicated answer for the newbie that I am. Ken suggested changing the date column type to text, doing the replacements to correct the bad dates, and then changing the type back to date. Worked great.

Thanks again for responding. I've see many of your solution posts in the forums and appreciate the fact that you are so willing to share your knowledge.