changing date from text to date

[lastnn30]

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

[Anonymous]

Hi,

I can't open your source. so, simulated this, and didn't see the issue. 

I hope this answer helps you.

[ribisht17]

@lastnn30 

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.xlsx

Please check 

Regards,

Ritesh

[Jihwan_Kim]

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 · Accepted solution]

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.)