Inconsistent date format in source data

[G_Whit-UK]

Hi

I am importing data from a PDF table.  One of the columns is a date field, and I've found examples where the date formatting is inconsistent (mm/dd/yyyy vs dd/mm/yyyy).  The majority of the PDF reports are in dd/mm/yyyy format, which Power Query is treating as the default.  Instances where the date is in the US format are resulting in errors.  How can I transform the instances where the date is in mm/dd/yyyy format?

(FYI:  The column header is "S/D")

Thanks

[AlB]

Hi @G_Whit-UK 

Can you share the PDF file, so that we can test the solution directly? You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[G_Whit-UK]

Hi @AlB ,

Unfortunayly I can't share the PDF reports as it has sensative client data on it.  I've attached extracts from two version reflecting how the date flips in the source files.29 Jan report extract

30 Jan report extract

I was wondering if there is a way to write an iferror statement where it then picks the date up in the mm/dd/yy format and converts it to the dd/mm/yyyy format.

[mahoneypat]

FYI that iferror in power query is try...otherwise (try this and do otherwise if an error).  You could add a column that first "try"s to parse out the day month and year from the delimiters "/" and use #date(yyyy,mm,dd) to make it into a date and having a similar expression on the otherwise part (with month and day reversed).

However, I expect you will have some issues when day and month and <=12 since you won't get an error.  If there another column in the data that could be used in an if ... then ... else so you could predict which rows needed which date conversion?

[Anonymous · Accepted solution]

Hi @G_Whit-UK 

Expanding on @mahoneypat suggestion.

You can test the entire piece of input data first to decide which data format to use. Add a column with something like try Date.From(...) otherwise "FAILED" and then test the added column for the presence of "FAILED" (group/count or filter/row count).

If all good use Date.From(...) to convert text to dates, otherwise Date.From(..., "en-US").

You can go even further depending on what date you load and how they are stored. E.g. if this is only same month data, i.e. report called 31 Jan 2020 only contains Jan data you can test that you do not have any months in the output other then contained in the report file name (this is to fight 1/11 vs. 11/1 cases).

Kind regards,

JB 

[dax]

Hi @G_Whit-UK , 

It seems to be related to data type , I think you could try to change type like below to see whether it work or not

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[blopez11]

Maybe you can initially treat the date column as text, and follow the below reference as a guide to create a new column using the try otherwise method for converting it to a date

https://www.thebiccountant.com/2016/06/22/advanced-type-detection-in-power-bi-and-power-query/