Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.