Column with mixed date types - half is mm/dd/yyyy, half is dd/mm/yyyy - How do I resolve?

[Jaylocks1990]

Hey all, 

Pretty new with PowerBI so please bear with me. 

I have created a MS flow that scrapes a Teams Channel and adds the content to an Excel table that has the headings:

Sender

Date/Time

Message ID (unique number attached to each message)

Content

Unfortunately, the UTC conversion step in the flow appears to have been off somehow because the date format switches over halfway through the month like so:

The issue is present in the source data, so PowerBI is recreating the same issue in power query:

This has happened every half month for the last 3 months, so I now have data that is half mm/dd/yyyy (which I don't want) and half dd/mm/yyyy which I do want. 

The spreadsheet which this has happened to is a live document, with many messages being added to it constantly, so I cannot do large modifications to it.

I have tried:

Setting the date type 'by locale' (I am in NZ) but this has not helped. 

Creating a custom column using an expression I found here, also from this forum. 

Exporting a total copy of the spreadsheet and manually trying to change the month column to text so I can identify it but I still need a way to be able to convert the numbers into correct order first before changing the month to text to prevent reoccurence.

Tried splitting the date column and recombining but this does not work because they are half correct, and half incorrect so I just kind of swapped the problem over. 

I can't seem to figure out a way to only modify the half of the column that I need to change, lots of really great advice and videos about how to change whole columns to the correct format but I can't find anything about how to do specific parts of the column that I need.

 Any help will be appreciated. 

Jaylocks

[mahoneypat · Accepted solution]

Ohhhh.   If you know that the incorrect format stops at a certain index value, you can just use this approach ...

=if [Index] <12345 then Text.BetweenDelimiters([Date],"/","/")&"/"&Text.BeforeDelimiter([Date],"/",0)&"/"&Text.AfterDelimiter([Date],"/",1) else [Date]

Does that work?  Not sure the index where it stops (or if the logic is more complext), but you get the idea.

Regards,

Pat