Forum Discussion
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
NewCutOff = SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(rounds[CutOff],"at ",""),"UTC",""),",",""))," ",", ")
I am not able to convert into datetime format using modelling or DATEVALUE any ideas would be helpful
Isn't it way much easier to convert the string to datetime data type by PQ?
or just a matter of clicks
2 Replies
Isn't it way much easier to convert the string to datetime data type by PQ?
or just a matter of clicks
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.
