question

CarrieE-0734 avatar image
0 Votes"
CarrieE-0734 asked HerbertSeidenberg-6295 commented

Fix inconsistent and weird date formatting in a column in PowerBI

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!

power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HerbertSeidenberg-6295 avatar image
0 Votes"
HerbertSeidenberg-6295 answered CarrieE-0734 commented
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can I do this using only Power Query Editor and not involving Excel??

0 Votes 0 ·
HerbertSeidenberg-6295 avatar image
0 Votes"
HerbertSeidenberg-6295 answered HerbertSeidenberg-6295 commented

PQ works the same in Excel and PowerBI.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I apologize for my ignorance, but how did you get the original table into the new date format? I'm not seeing the steps to make that happen, and whenever I try it doesn't recognize the date format with the year after the time. Also, when I tried to put that formula in on PowerBI, it didn't recognize it Types.

I'm considering pulling the string apart at space delimiter and then trying to put it back together with crazy if statements. surely there is an easier way?

I'm so thankful for your help in figuring this out. Thank you in advance for your patience and response.

0 Votes 0 ·

You're so jolly right.
Updated file.
Same link.

0 Votes 0 ·