cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
robarivas Helper V
Helper V

Convert Mixed Data Type Column to Date Type

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?

1 ACCEPTED SOLUTION

Accepted Solutions
robarivas Helper V
Helper V

Re: Convert Mixed Data Type Column to Date Type

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.

View solution in original post

2 REPLIES 2
Super User I
Super User I

Re: Convert Mixed Data Type Column to Date Type

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 Helper V
Helper V

Re: Convert Mixed Data Type Column to Date Type

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.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors