M-Language: How to convert date format from European to US?

[RafaelKnuth]

I was wondering how to write a function in M that would convert date formats from EU to US?

Example: December 31st, 2010 needs to be transformed from 31/12/2010 to 12/31/2010

Below an exemplary Excel function - I was wondering if I can perform that date transformation with M in my Power Query?

I went through the Power Query M Function reference, but I couldn't find a function that allows me to reverse a value in any desired order (I only found one function that allows me to reverse in one specific order: 1234 would become 4321 but that's not the solutiuon I want).

As a temporary workaround, I would split the Date EU column into 3 columns by the slash delimiter, and then merge those columns in reversed order. But that's a very creepy, unelegant solution IMHO.

Any ideas? Thanks a lot in advance!

[v-yulgu-msft]

Hi @RafaelKnuth,

You can try Date.ToText function.

=Date.ToText([date],"MM/dd/yyyy")

Besides, you can try to convert date using Local.

Best regards,

Yuliana Gu

[RafaelKnuth]

Hi Yuliana,

thanks for your help!

It partially worked - the solution you suggested didn't work on my computer (I tried different variations), Reza's solution did work, but only in one specific case. To me it seems that proper date conversion depends on multiple factors (such as Locale settings). That's a tricky one 😉 There are so many odd date formats people are using.

Best,

Rafael