Transform "some" (and not all) values in a column?

[GWTF]

Dear community,

I'm experiencing an issue which is rooted in the base table I am using but I can not edit this base table. The problem is that we have multiple scripted workflows populating fields in an ArcGIS SDE database (Oracle): in the past, these workflows logged activities by date & time using for instance the the following -US- date local:

07/24/2016 so MM/DD/YYYY

Overtime, there have been changes over the date local used on the Arc SDE Server and for some time now, dates are being stored in the Dutch date local. For instance:

24-06-2016 so DD-MM-YYYY

By the way: these log-entries are in a text field (besides the date notation there are also descriptions like " 07/24/2016: edits perfomed by user XXXXXX") from which I have to isolate the dates (no problem there).

But, like I mentioned these dates are all in the same column. I have been busy with duplicating the log-entry field, splitting it in order to get the necessary dates, duplicating the table, selecting the dates by the way they are formatted (filter by "/"or "-") and duplicating the entire table with the Dutch locale filter results (DD-MM-YYYY) and duplicating the filtered table with the dates in the US date locale and changing this type with locale. And then I would combine these tables with the "append" option.

This works, but I can't help thinking there should be an easier solution to this. By for instance only calculating/changing a selecting of the values in the duplicated column...

Am I right or am I wrong?

Thanks and all the best from The Netherlands,

Wilco

[edhans · Accepted solution]

Maybe I am understanding the full issue, but seems to me a custom column would work. This will work if the dates are isolated as text.

if Text.Middle([Column1],2,1)="/" then Date.FromText([Column1], "en-US") else 
if Text.Middle([Column1],2,1)="-" then Date.FromText([Column1], "nl-NL") else null

It assumes US dates have a "/" and Dutch dates have the "-".

If your dates are still in the source column, you'd need to wrap the [Column1] reference in the Date.FromText function with Text.Start([Column1,8]), so Date.FromText(Text.Start([Column1,8]), "en-US").

This is showing the result on a US English PC. Your result would be viewed using your locale.

If the nl-NL isn't the right culture code, try nl-BE (Belgium), or see this page for a longer list. You can also get your current culture by typing in = Culture.Current in a blank query.

[GWTF]

First of all I would like to apologize for my very late response since I have been occupied with other challenges and the occasional quality time with the family 😀

But many, many thanks to @edhans: you've provided an excellent answer to my question! It works flawlessly and I am very happy now.

All the best,

Wilco Loth

[edhans]

Glad I was able to assist @GWTF and thanks for getting back to the thread and marking it solved! Have a great rest of the week.