Why there is no direct way to change date formats in Power Query?

[LynchPin]

Hi everyone here!

I am not asking your help for any technical help on any specific data set, right now. Just asking a question I am living with.

I respect Power Query for it's obvious automation capabilities. But as an Excel user, sometimes I used to think why this tool lacks some capabilities that were everyday nightmares, at least for someone on the planet.

Some possible date formats.

1. Dec 24, 2012
2. 12/24/2012
3. 12-24-2012
4. 24/12/2012
5. 2012/12/24
6. 20121224
7. Dec 24 2012
8. 12242012
9. Dec-24-2012
10. 24 Dec 2012
11. 2012Dec24
12. 201224Dec

If I have dates like these in my data or I receive them from xyz software,

1. I can convert ONLY SOME OF THEM to my format i.e. DDMMYYYY or MMDDYYYY either with Excel or PQ. I feel strongly that even PQ cannot convert ALL OF THOSE DATE FORMATS to either DDMMYYYY or MMDDYYYY, is it true or not?
2. Even if there's a way it is not direct and involves complex VBA code if it is Excel; M code if it is Power Query.

If I am commenting this not knowing the real capabilities of Excel or PQ, Please enlighten me on this, I will bow my head to you all Gurus.
If you find any article or blog or video about this, please catch my scruff and point me to the right direction.

Thank you all !!
LynchPin

 

[Bo_Ry]

Try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TDate = Table.TransformColumns(Source,{{"Date", each try Date.From(_,"en-US") otherwise try Date.From(_,"en-GB") otherwise try Date.From(Number.ToText(Number.From(_),"00-00-0000")) otherwise Date.From(Text.End(_,5)&Text.Start(_,4)) , type date}})
in
    TDate

|


But without a consistent format, there is no way to distinguish 01/02/2021 for 1 Feb 2021, or Jan 2, 2021

 

[LynchPin]

Hi Bo_Ry,

All these different date formats are not existing in a column at a time. That means, you take a particular date format, the remaining data follows the same format. It's not the mix of all the above date formats in a column.

So my question is, do Power Query has the capability to transform each of those above date formats into DDMMYYYY or MMDDYYYY???

Thank you

LynchPin