Forum Discussion
Post Patronchanging date from text to date
Hi
I have a column which has [Text Date Text] in one cell. I used power query to split text and date into different columns. Now I have the Date in one column but in Text format. I click on ABC of Power Query and change the format to Date but I got many error message inside the column. Please see screenshots and I also attaching the link to the file. How can I fix that? Coverting the format of date from text to date in power query. Thank you very much.
The file is here
https://drive.google.com/file/d/1QSYjZ_9rPKZrKjSWMXnJfrVZPlu1jmlE/view?usp=sharing
Yeah, I'm pretty sure this is the issue. This uses the optional "culture" argument of Table.TransformColumnTypes to pick the appropriate way of interpreting the date format.
Another way to do this is to tweak the M code directly. If the date format is dd/mm/yyyy, then you can write
Table.TransformColumnTypes( #"Split Column by Delimiter", { {"Item.1", type text}, {"Item.2", type date}, {"Item.3", type text}, {"Sales", type number} }, "en-IN" )If the date format is mm/dd/yyy, then use "en-US" for the last argument instead.
(There are plenty of other cultural choices but these are the two I remember for the sake of date interpretation.)
4 Replies
- Jihwan_Kim
Super User
Hi,
I cannot see the information in Power Query Editor, since the source file is not accessible.
However, I assume that PBI desktop is setup as to show USA date structure, but the data shows different date structure.
I suggest try the below.
In Power Query Editor, select the column -> right click -> Change type -> Using locale -> Data type: Date -> Locale: English(Netherlands) or English(UK)
- AlexisOlson
Yeah, I'm pretty sure this is the issue. This uses the optional "culture" argument of Table.TransformColumnTypes to pick the appropriate way of interpreting the date format.
Another way to do this is to tweak the M code directly. If the date format is dd/mm/yyyy, then you can write
Table.TransformColumnTypes( #"Split Column by Delimiter", { {"Item.1", type text}, {"Item.2", type date}, {"Item.3", type text}, {"Sales", type number} }, "en-IN" )If the date format is mm/dd/yyy, then use "en-US" for the last argument instead.
(There are plenty of other cultural choices but these are the two I remember for the sake of date interpretation.)
- ribisht17
I don't see any issues with a similar dataset.
Can't open your file, not in the correct format
DataSource.Error: Could not find a part of the path 'C:\Users\153707\Desktop\PowerBI\weeks\7\More PQ Examples.xlsx'.
Details:
C:\Users\153707\Desktop\PowerBI\weeks\7\More PQ Examples.xlsxPlease check
Regards,
Ritesh
