Forum Discussion

krishnasen's avatar
krishnasen
New Member
3 years ago
Solved

Want to Convert a date String to date format

Hi, I am using a third a party API to give a date time in the format September, 11, 2021, 10:55:43 or September 11 2021 10:55:43 any idea            I am already using a Dax to give a uniform format ...
Need Help
Tips and Tricks
Tutorial Requests
  • ThxAlot's avatar
    ThxAlot
    3 years ago

    Isn't it way much easier to convert the string to datetime data type by PQ?

     

    or just a matter of clicks

Adamboer's avatar
Adamboer
Responsive Resident
3 years ago

To convert the date time in the format "September, 11, 2021, 10:55:43" or "September 11 2021 10:55:43" to a datetime format in Power BI, you can use the following DAX formula:

NewDateTime = IFERROR(DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ROUNDUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER([Date Time]),"am"," AM"),"pm"," PM"),",",""),"at",""),"utc",""),"-"," ")," "," "),"/","-"),"st",""),"nd",""),"rd",""),"th","")&" "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER([Date Time]),"am"," AM"),"pm"," PM"),",",""),"at","")),BLANK())

This formula first converts the input date time to lower case and then replaces any unnecessary text with empty strings to get a consistent format. It then uses the DATEVALUE function to convert the resulting text string to a datetime value.

Note that this formula assumes that the input date time is in the "Month, Day, Year, Hour:Minute:Second" or "Month Day Year Hour:Minute:Second" format. If the input format is different, you may need to adjust the formula accordingly.