Forum Discussion
Converting dates from MM/DD/YYYY to DD/MM/YYYY in Power Query
I have a column that contains dates in the format of MM/DD/YYYY (seel below picture)
I need to convert this date format to DD/MM/YYYY.
Please note the column in which the dates are stored has a mixed data type ... some dates are in "Text" and others "Whole Number". Whe i try to conver the column to a "Date" format some values are converted and others "Error".
4 Replies
- amitchandak
Super User
CharlieVan , You have to based on each row
These functions will help
Power Query - Text.Start, Text.Middle, Text.End: https://www.youtube.com/watch?v=vky4wPqm0O0
Text.Length: https://youtu.be/DsHgFzNyTdM
and Number.IsNaN
Hi amitchandak ,
Thanks for the infomration.
I managed to use the "Text.Start, Text.Middle, Text.End" function in power query and the column was created as required (see below).
However when i added the Date.FromText to the custom column query i get a number of errors - see below:
- Ahmedx
could you share your pbix-file? Or create an example file which reproduces your issue?
- Ahmedx
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhmQq2FbhI0XPLIeS?e=rmicvdtry DateTime.FromText( Text.Split( [date], " " ){0} &" "&Text.Split( [date], " " ){1},[Format = "MM/dd/yyyy h:mm:ss"]) otherwise try DateTime.FromText( Text.Split( [date], " " ){0} &" "&Text.Split( [date], " " ){1},[Format = "M/dd/yyyy h:mm:ss"]) otherwise DateTime.FromText( Text.Split( [date], " " ){0} &" "&Text.Split( [date], " " ){1},[Format = "MM/dd/yyyy h:mm"]) -------- OR -------- try DateTime.FromText( Text.Split( Text.From( [date]), " " ){0} &" "&Text.Split( Text.From( [date]), " " ){1},[Format = "MM/dd/yyyy h:mm:ss"]) otherwise try DateTime.FromText( Text.Split( Text.From( [date]), " " ){0} &" "&Text.Split( Text.From( [date]), " " ){1},[Format = "M/dd/yyyy h:mm:ss"]) otherwise DateTime.FromText( Text.Split( Text.From( [date]), " " ){0} &" "&Text.Split( Text.From( [date]), " " ){1},[Format = "MM/dd/yyyy h:mm"])
