Forum Discussion
Convert Data Type from Text to Date and into a Different Date Format
Coming back to this, I realize the function I proposed is not the right one. Date.ToText assumes your values are already Date type, but yours are text.
Did you say some of your values are in general format vs date? Does that mean you input table looks like the column on the left (dates stored as integers and date formats).
If so, you can use a formula like this to convert each type differently. We may now be beyone the more elegant solution you initially requested, but I will share this anyway in case it helps.
= if Text.Contains([Date], "/") then Date.ToText(Date.FromText([Date]), "dd/MM/yyyy") else Date.ToText(Date.AddDays(#date(1899,12,31), Number.FromText([Date])-1), "dd/MM/yyyy")
The above is an added custom column, and you should be able to convert it to Date in the following step.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
KunalL - Not sure why some of your values would cause an error. In terms of what you did, it's funny because that is the exact approach that I use when people post their data to the forums that are mm/dd/yyyy and I want it in dd/mm/yyyy. I think I posted an idea to make it a button or option to convert from one to the other.
I got an answer by looking up the error message and searching for a post for that here - I saw your response here.
I saw that its the CSV file that has the issue. Some of the cells in the column are formatted General while some are Date. Those with General formatting are the ones that are shown with the Error link saying 'DataFormat.Error: We couldn't parse the input provided as a Date value. Details: 8/26/1961.' While this may be out of the scope of Power BI, I wonder if you could point me in the direction of the resolution?
Thanks.
