Data formatting issue

[maricci1]

Hello, I have a problem, when I import data from the MySql database, the date is of type text in the form of mm/dd/yyyy, I can't change the database....and when I transfer that column to date, short date, it just creates mm.dd.yyyy, and then there is a problem, if dd is greater than 12, it gives an error......how can I transfer that column to dd.mm.yyyy

Thanks

[sayan_07]

the easy way is just Paste this M Code :  = Date.FromText([Date column Name], "en-US")

2nd Method :

Right Click the Date column in Power Query and Click data type Correct the Date Type

Then Go to Using Locale and change the Locale to English (United States)

[v-aatheeque]

Hi @maricci1 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

[v-aatheeque]

Hi @maricci1 

We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.

[ryan_mayu · Accepted solution]

@maricci1 

maybe you can Using Locale and select the proper locale to get the correct date type.

[pankajnamekar25]

Hello @maricci1 

Try this

Add Column → Custom Column and use this formula:

= Date.FromText(Text.Replace([YourDateColumn], "/", "-"), [Format="en-US"])

But if that gives errors, try this manual split + reorder method:

let
parts = Text.Split([YourDateColumn], "/"),
month = Number.FromText(parts{0}),
day = Number.FromText(parts{1}),
year = Number.FromText(parts{2})
in
#date(year, month, day)

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn