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
I tried what you suggested. I got an error that says "Expression.Error: We cannot convert the value "1/1/1997" to type Date".
The step is shown in PBI as (may be this helps you resolve)
= Table.AddColumn(#"Promoted Headers", "Custom", each Date.ToText([date], "dd/MM/yyyy"))
I also tried using mm instead of MM but it didn't work.
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
Thanks, the solution you suggested did not work, I got the same error message. However, I may have found a solution. Not sure how, but the solution suggested by myndworkz on page 2 here worked for me.
I changed the Locale setting in Options from English (United Kingdom) to English (United States). Also tested it with the table where I had converted the date using my unelegant method as described in the issue description (call it table A) and on the table coming in from the original CSV with formatting issue (table B). When I made this change and refreshed, table A fell apart while table B displayed dates corrected in DD/MM/YYYY. When I changed it back to English (United Kingdom), a refresh broke table B but table A now displayed dates in DD/MM/YYYY.
Would you know why?
I don't change the default locale so haven't seen that behavior. Not sure why that worked, but hopefully you are able to move forward now.
Regards,
Pat
Hello mahoneypat
Thank you for your solution.
Can you please share more detials about how to convert text like row no. 2 and 4 into date?The integer version of dates are relative to that starting date, so for those rows (those that don't have "/"), the expression adds that many days to it (-1).
Pat