Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Jaylocks1990
Frequent Visitor

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

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:

2020-06-08 18_07_03-All Channel Message capture.xlsx  -  Repaired - Excel.png

 

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

2020-06-08 17_31_37-Table2 - Power Query Editor.png

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

 

2 ACCEPTED SOLUTIONS

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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) 

2020-06-26 15_24_22-Window.png

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

2020-06-26 15_27_09-.png

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

2020-06-26 15_28_50-Window.png

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. 

 

 

View solution in original post

12 REPLIES 12
v-zhenbw-msft
Community Support
Community Support

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.

 

C 1.jpg

 

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_

 

C 2.jpg

 

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.

 

C 3.jpg

 

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

 

C 4.jpg

 

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

 

C 5.jpg

 

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.

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'.

2020-06-09 20_43_23-Site Support analysis - Power BI Desktop.png

 

It has switched over correct ones and added a dash. 

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

2020-06-09 20_47_10-Site Support analysis - Power BI Desktop.png

 

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

 

Hey @v-zhenbw-msft 

 

Thanks for replying. 

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

2020-06-09 19_19_09-Custom Column.png

Hi @Jaylocks1990 ,

 

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

 

C 7.jpg

 

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
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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 : 

2020-06-09 14_55_14-Site Support analysis - Power Query Editor.png2020-06-09 14_52_39-Site Support analysis - Power Query Editor.png

 

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

 

 

 

 

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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: 

2020-06-10 11_31_45-Site Support analysis - Power BI Desktop.png

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

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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) 

2020-06-26 15_24_22-Window.png

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

2020-06-26 15_27_09-.png

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

2020-06-26 15_28_50-Window.png

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
Super User
Super User

@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))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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? 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.