Forum Discussion
Helper IIInconsistent date format in source data
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
Community ChampionHi 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-UKHi 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 extract30 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
Microsoft Employee
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?
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
blopez11Super User
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/
