transforming text to date gives error

[chahineatallah]

hello, i have a folder with .txt files, as usual i do from data then from folder

so both text files are combined, in text file i have dates which are imported as text data type, when in power query i try to change data type to Date type, some are changed others are giving error, cant figure out what is the issue

[chahineatallah]

Thanks, local was useful 👍

[PhilipTreacy · Accepted solution]

Hi @chahineatallah 

The screenshot of your regional settings shows that your date format is d/m/y.

This screenshot shows that the data you are importing is in m/d/y format

To import these dates correctly what you need to do is Change the date using Locale.

Right-click on the column header and then on Change Type -> Using Locale

Then choose Data Type -> Date and Locale -> English (United States)

Click OK and you should be done.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[chahineatallah]

[chahineatallah]

Hi this is the error message

Also some sample data which is a .txt file , so the date when its imported to power query and i press in the column to change type to date, some dates are changed while others show error

Date Sales Product
1/2/18 3 Carlota
1/5/18 2 Carlota
1/7/18 25 Carlota

[amitchandak]

@chahineatallah , what is your system date setting , 1/15 is fine. Is System date setting is UK /AUS/IND format ?

[amitchandak]

@chahineatallah , Click on the error and share that. Also share some sample data

[chahineatallah]

nope its month day and year

[amitchandak]

@chahineatallah , Are you dates is in DD/MM/YYYY format. If so you need to have following setting

https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...