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 ,
if you date are in UK format and you want to work with those -https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...
Check an option in data format (screenshot at last) or under formatting string for that :https://docs.microsoft.com/en-us/power-bi/desktop-custom-format-strings
Else create a date string like (if it is detected as date )
Format([Date],"dd-mm-yy")
Format([Date],"mm-dd-yy")
Format option
Thanks, this is another way. But I would like to do it in Query Editor. Because I just saw a video where it was recommended that column additions be done in Query Editor first and then as any Calculated Column - please correct as per your experience.