Convert Mixed Data Type Column to Date Type

[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?

[Jimmy801]

Hello @robarivas 

you could try this approach

- Create one function to read a CSV-file

- include in this function another function to check the data type and depending on the feedback of this answer you can then apply different Table.TransformColumnType

- apply to every CSV-file the first function create and combine the result

here the function to identify the datatype (pass a column of your single CSV-file)

 (list as list) =>
    let
        selectedItems = List.FirstN(list, 200), 
        itemTypes = List.Transform(selectedItems, each Value.Type(_)),
        listItemType = Type.Union(itemTypes)
    in
        listItemType 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[robarivas · Accepted solution]

Thanks @Jimmy801 

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.