How to Handle Data that can't be converted to date

[Anonymous]

Hi Guys,

Good Day!

I have a column(text) that should be converted to date and it seems to have erronious data (strings that on a different format and added string mostly inputed by the user). I am using that data to calculate DateDifference is there anyway to handle erronious data. Can I filter it?

Note:  Data input have different date format. 

Any suggestion for work around Thanks in advance

Cheers 

John 

[croberts21]

In Perl I would use regex to pick out something that looked like a date and it would be a one-line piece of code. PBI doesn't have regex. 

Can you provide 5-10 examples of different dates you see? We need to see how variable the dates really are. 

On another note, data entry in a specified format is really important and managers need to realize that. Date fields should contain only dates and should not contain any other info like letters, spaces, or other odd characters. 

[tamerj1]

Hi @Anonymous 

how does it exactly look like?

[Anonymous]

Hi @tamerj1 

Some of the datestring has not been cleanse for example "2022-06-07 lvm",

there are some additional string "notes" after the date itself. I'm just looking for a way to handle it.. 

Thanks

[tamerj1 · Accepted solution]

@Anonymous 
Using DAX you can create a new column: https://www.dropbox.com/t/s85UTi1xHD9hwKVk

Date Corrected Format = 
VAR StringPath1 = SUBSTITUTE ( SampleData[Date], "-", "|" )
VAR StringPath2 = SUBSTITUTE ( StringPath1, "/", "|" )
VAR StringPath3 = SUBSTITUTE ( StringPath2, " ", "|" )
VAR TheYear = VALUE ( PATHITEM ( StringPath3, 1 ) )
VAR TheMonth = VALUE ( PATHITEM ( StringPath3, 2 ) )
VAR TheDay= VALUE ( PATHITEM ( StringPath3, 3 ) )
RETURN
    DATE ( TheYear, TheMonth, TheDay )

[Anonymous]

Hi @tamerj1 

Thank you very much for your help. I will try playing it around as there is no definite format of dates in my data. Thanks

[tamerj1]

@Anonymous 

If you can identify as mich cases as you then we can modify the code to adapt as much as possible. I believe it can be done. I can help on this but I cannot do anything without knowing most of the cases. 

[Anonymous]

Hi @tamerj1 

Good Day !

Here are some of example datas

January 4, 2022

06/14/2022

Jan 20 2022

2022-06-16

1/17/22

That's all the format that I needed to handle as well as some non date string that should be filter out

Thanks

[croberts21]

This is not an ideal situation for this date. Since you need this date to be consistent to write reports, this is the point where you get management involved to make a single field only for this date, and restrict data entry to one specific date format that is always the same.

IMO, part of my job as a programmer is to make sure management makes more informed decisions about database design good practices, to make accurate reports, and to improve the function of the team as a whole.

Also, for dates like 06/14/22, you have to assume it is always in mm/dd/yy format, or dd/mm/yy, you cannot assume both. So choose one assumption, and make sure the users know this is the only format that will be used, and get management's support on this.

[tamerj1]

@Anonymous 
Everything can be dealt with except of one thing. There is no way on earth to identify the day from the month. If an entry says 02/04/2022 even the person who created this beautiful column cannot identify which one is the month and which one is the day. There is no way even manually one by one to get the correct date out of this column because only god knows what was the shape of the very original source of this data.

[Anonymous]

Hi @tamerj1 

Yeahh you are right about that, the poor UI design is the cause of this issues. Anyway one thing I think possible is to filter out all that kind of data that can't be converted to date 

Thanks

[tamerj1]

@Anonymous 

I'll have 2nd look at it tomorrow. Chances of success are limited. 

[Anonymous]

Hi @amitchandak ,

Is there any general functionality that can be use as IF logical condition that if the "datestring" can't be converted to date then use default date. Thanks

[amitchandak]

@Anonymous . share some sample data, we need define some rules