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
Helper IIHi 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.
Microsoft EmployeeFYI 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