Converting dates from MM/DD/YYYY to DD/MM/YYYY in Power Query

[CharlieVan]

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".

[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=rmicvd

try 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"])

[Ahmedx]

could you share your pbix-file? Or create an example file which reproduces your issue?

[amitchandak]

@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

[CharlieVan]

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: