Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Isn't it way much easier to convert the string to datetime data type by PQ?
or just a matter of clicks
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
Isn't it way much easier to convert the string to datetime data type by PQ?
or just a matter of clicks
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.