Forum Discussion
Change Type locale not working in query editor for Date
After convering it to Date/Time using Change Type or Change Type using Locale (en-US),it displays wrong as below
Please advise if you have any leads..Thanks in advance for your help !!
ashish18,
Once the column is in a date type, you can change the format by clicking on the column outside of the power query editor and selecting column tools, then format:If you found this helpful, consider giving some kudos. If I answered your question or solved your problem, mark this post as the solution to help future users find it quickly.
Hi, or you can try below M. Replace with your table and column name. I put this as function only because it's good for reuse later
let Source = YourTable, //YourTable to be reploaced ConvertedTable = Table.TransformColumns( Source, { {"Column1", each //Column1 to be replaced let Day = Text.Start(_, 2), Month = Text.Middle(_, 3, 3), Year = Text.Middle(_, 7, 2), Time = Text.End(_, 8), FullDate = Day & " " & Month & " 20" & Year & " " & Time in DateTime.FromText(FullDate), type datetime} } ) in ConvertedTable
15 Replies
- MasonMA
Super User
Hi, one of the workarounds would be creating a function using below M,
(DateText as text) as datetime => let Day = Text.Start(DateText, 2), Month = Text.Middle(DateText, 3, 3), Year = Text.Middle(DateText, 7, 2), Time = Text.End(DateText, 8), FullDate = Day & " " & Month & " 20" & Year & " " & Time, ConvertedDate = DateTime.FromText(FullDate) in ConvertedDatethen invoke it in Table.TransformColumns()
let Source = Source, ConvertedTable = Table.TransformColumns( Source, {{"Column1", each Convert(_), type datetime}} ) in ConvertedTable- MasonMA
Hi, did you give my solution a try? Your original 'Create' column is of Text type.
- tayloramy
Hi ashish18,
What date format are you wanting it to display as?
You can select different date formats in the format dropdown, or use a custom format using a pattern.If you found this helpful, consider giving some kudos. If I answered your question or solved your problem, mark this post as the solution to help future users find it quickly.
- tayloramy
ashish18,
Once the column is in a date type, you can change the format by clicking on the column outside of the power query editor and selecting column tools, then format:If you found this helpful, consider giving some kudos. If I answered your question or solved your problem, mark this post as the solution to help future users find it quickly.
v-saisrao-msftCommunity Support
- v-saisrao-msft
Hi ashish18,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
- v-saisrao-msft
Hi ashish18,
We haven’t heard back from you in a while regarding your issue. let us know if your issue has been resolved or if you still require support.
Thank you.
