Power Query convert text to date

[robarivas]

Pulling many millions of rows from dozens of csv files. Some of the files have a date column coming in to power query in a serial format while others have that same column coming in the mm/dd/yyyy format. Although Power Query initially "sees" the column in the "Any" format, you can tell the entire column is inherently text. So when I change the column data type to Date the mm/dd/yyyy rows convert successfully but the serial format cells do not. If I change those serial format cells to number type first then the date conversion works but that then causes the mm/dd/yyyy cells to fail. So I sort of have a mixed format situation. I can overcome this with the try...otherwise pattern but that seemed to bring my query to a crawl, which is a major problem when dealing with so many millions of rows. Any ideas how I can efficiently get the mixed format column successfully converted to date?

[RicardoTeixeira]

Function for Power query that converts a text with dates to dates based on a kwon format.

(Creating a function from an existing endpoint is a simple process. Right-click on the endpoint name and choose Create Function.)

Over the new function, in advace editor past the code:

let
Source = (dateColumn as text,DateSchema as text) => let
MyDelimiter = Text.Range(DateSchema,Text.PositionOfAny(DateSchema,{"/","-",".","\","_"}),1),
DateList = Splitter.SplitTextByDelimiter(MyDelimiter, QuoteStyle.None)(dateColumn),
SchemaList = Splitter.SplitTextByDelimiter(MyDelimiter, QuoteStyle.None)(DateSchema),
TableDate = Table.FromColumns({DateList,SchemaList}),
myDay = "0" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "D" )[Column1]{0}),
myDD = Text.Range(myDay,Text.Length(myDay)-2,2),
MyMonth= "0" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "M" )[Column1]{0}),
myMM = Text.Range(MyMonth,Text.Length(MyMonth)-2,2),
myYear= "20" & Text.From(Table.SelectRows(TableDate,each Text.Upper(Text.Range([Column2],0,1)) = "Y" )[Column1]{0}),
myYYYY = Text.Range(myYear,Text.Length(myYear)-4,4),
DateByParts = myYYYY & "-" & myMM & "-" & myDD,
DateCleaned = Date.FromText(DateByParts)
in
DateCleaned
in
Source

Then invoke the function in a new column.

[stretcharm]

I would add a column using an expression like this that checks if the conversion works.

if Value.Is(Value.FromText([DateValue]), type datetime) then [DateValue] else if 
Value.Is(Value.FromText([DateValue]), type date) then [DateValue] 
else null

If there are lots of different values it might be easier to have a column for each type, you could then replace errors with null. Add a final column to pick the non null date.

Adding column using examples can help get build powerquery expressions. This video shows how to make a date from other columns.

https://www.youtube.com/watch?v=t1WLdABqZeQ

[robarivas · Accepted solution]

Thanks @stretcharm and @AlexAlberga727 

Here is what I ended up trying and it worked, at least for my particular situtation. The key is to use Value.FromText wrapped around Text.Trim

= Table.TransformColumns(PreviousStepOrOtherQuery,{{"AMOUNT", each Currency.From(Value.FromText(Text.Trim(_))), Currency.Type}, {"QUANTITY", each Int64.From(Value.FromText(Text.Trim(_))), Int64.Type}, {"ORDER_DATE", each DateTime.From(Value.FromText(Text.Trim(_))), type datetime}, {"SALE_DATE", each Date.From(Value.FromText(Text.Trim(_))), type date}})

I tried getting rid of the each and underscore pieces of the code above but that didn't want to work. However, the M code above works just fine.

[AlexAlberga727]

I would interested in a solution to your issue. I believe a lot of the data manipulation will need to be done within the power query editor before pulling the records in.

Could you provide some examples of the data your're attempting to reformat?