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

[v-zhenbw-msft]

Hi @Jaylocks1990 ,

We assume that you have a condition that can distinguish the wrong date and the correct date. You can refer the following steps;

1. Add an index column in Power Query, the index column as a basic for judgement.

2. Then we can create a calculate column to get the correct column.

Column = 
var left_ = LEFT('Table'[Date],2)
var mid_ = MID('Table'[Date],4,2)
var right_ = RIGHT('Table'[Date],4)
var Month_ = IF([Index]<MAX([Index])/2,[Date],mid_&"/"&left_&"/"&right_)
return
Month_

Or we find an interesting function, but we don’t know if it will be helpful for you. You can refer the following steps.

1. In data view, change the data type to date. It will change the wrong date to correct date.

2. Then we can use the Copy Table function and copy it to a new table.

3. At last we need to change the type with Locale, the result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jaylocks1990]

Hey @v-zhenbw-msft 

Thank you for your patience!

So I tried the DAX formula, and I had some mixed results.

I named the calculated column 'OKDate'.

It has switched over correct ones and added a dash. 

I also definitely still have some dates in the American format too:

The data type in all cases is set to text. 

I have tried the route with converting date by locale, unfortunately this has not helped. I have access to the source data, and I can verify through there that the messages are from that time. 

One of the columns is a unique identifier number assigned to each message also. 

Jaylocks

[Jaylocks1990]

Hey @v-zhenbw-msft 

Thanks for replying. 

I can't seem to make your custom column formula work? 

[v-zhenbw-msft]

Hi @Jaylocks1990 ,

The formula is a DAX formula, please create a calculate column in Power BI Desktop data view. 

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Since this all happened this year (i.e., we've only had single digit months so far), you can use this formula with your date column formatted as text.  In the query editor, make the first changed type step for that column a type text (I assumed it is named Date), and add a customn column with this formula:

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

Here is a full example M query with example data you can put in a blank query if interested.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTWNzDWNzIwMlCK1YlWMtY3NELwjPQNDRE8A1OE0lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains(Text.BetweenDelimiters([Date],"/","/"),"0") then Text.BetweenDelimiters([Date],"/","/")&"/"&Text.BeforeDelimiter([Date],"/",0)&"/"&Text.AfterDelimiter([Date],"/",1) else [Date]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}})
in
#"Changed Type1"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Jaylocks1990]

Hey @mahoneypat 

Thanks for that, I have created the custom column with the expression you wrote above and called it 'CleanedDate'. It has successfully switched the mm over with dd  in the incorrect dates, but also has switched over dd and mm in dates that were previously correct e.g : 

So how would I go about merging these columns selectively to get one column with all correct date formatting?

[mahoneypat]

Sorry.  My expression assumed that only incorrect dates had zeros in the middle, so it won't work if that is not the case.  I also thought you were seeking MM/DD/YYYY format.  If there is some logic that is only true for incorrect dates, hopefully you can use an approach like that.  My hope that it would create a new column for you in one step, and you could delete the original.

Regards,

Pat

[Jaylocks1990]

Hey @mahoneypat , 

So I was thinking this. 

Because of creating these custom and calculated columns that unselectively reverse the dd/mm from my original date column, I have all the correct dates, they are just scattered through two columns now, e.g: 

Would there be a way, perhaps using the index column for reference, to selectively combine these columns? 

So from my example I would be able to have an expression that says ' Merge from these columns from index 11522-11515' that I would be able to keep using as I went down the data and used the source to confirm which rows are correct?

Does that make any sense at all?

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

[Jaylocks1990 · Accepted solution]

Hey @mahoneypat, 

Thanks for your suggestion here earlier, I apologise for not getting back to you. 

The solution I went with in the end was: 

1. Open up the query editor

2. Select all the obviously wrong values from the filter list on the date column, fortunately I caught this before it was not clear which way round it should go (without going back to source data and confirming the message contents there) 

3. Manually change the incorrect dates round to correct with the 'Replace values' button

4. Once I did that I went to the applied steps window and removed the 'Filtered Rows' steps:

Step 4 was what I was missing in a previous attempt. 

This then gave me a column full of correct dates that could be used for analysis. 

Many thanks all for your assistance here, I was under a lot of pressure to sort it out. 

[amitchandak]

@Jaylocks1990 , if you can logic to identify what is there in dd-MM-YYYY, those you can convert into date like

Date(right(Table[date],4),mid(Table[date],4,2),left(Table[date],2))

[Jaylocks1990]

Hey @amitchandak,

Thanks for taking the time to reply. 

I wonder if you wouldn't mind expanding a little more on how exactly to use the expression you have shown? I have tried to create a custom column using it but I don't think I am using the syntax correctly?