Fix inconsistent and weird date formatting in a column in PowerBI

Question

In PowerBI, I am trying to clean up some incoming data to follow consistent formatting.

I have a column which has a text string meant to represent date and time. The format of the data from the source changed over time, so the data is in one of two formats.

Within the "StartDate" Column, some values look like this:
YYYY-MM-DD HH:MM:SS AMPM (Example: 2021-03-25 05:48:12 PM)

Others look like this:
ddd mmm d HH:MM:SS YYYY (Example: Thu Mar 25 17:48:12 2021)

My goal is to end up with two columns like this:
"Start Date" column ... format: YYYY-MM-DD (Example: 2021-03-25)
"Start Time" column ... format (24 hour): HH:MM:SS (Example: 17:48:12)

In plain explanation of steps, I was thinking I need to do something like:

1. If data starts with numbers, then translate to the Date = first 11 characters in format of MM/DD/YYYY ... Time = last 12 characters in HH:MM:SS AMPM format
2. If data doesn't start with numbers, then cut off the first 4 characters (day of week), move the last 4 characters (the year) to the beginning, and then translate to the Date = first 11 characters in format of YYYY mmm d ... Time = last 8 characters in HH:MM:SS format
3. if source is blank = leave as null

Thanks in advance for helping a newbie!

Answer 1

Excel 365 Pro Plus with Power Query.
Transform date/time formats.
https://www.mediafire.com/file/vsnuxmk8edv9iur/03_26_21b.xlsx/file
https://www.mediafire.com/file/55oz8ejve7f9nnz/03_26_21b.pdf/file

Answer 2

PQ works the same in Excel and PowerBI.