Different date formats in the same column

[Anonymous]

Hello. I have a table with a date column. But it has different formats and it's stressing me out. It's like in the example.

01-07-2021	Januari 7th 2021
01-07-2021	1st of july 2021

How can I fix this. I already searched the entire community and nothing helped me. Thanks in advance

[negi007]

@Anonymous You can split the value of the column into two different column. You can use powerquery window for spliting the data. One column can have valid date data and other column can have text which is same as your date. 

[VijayP · Accepted solution]

@Anonymous 

in Power QUery Editor Create another Column Indicating the Date Format like US / UK (let us say Name of the column "Format") , 

For example in your sample Data first Date is of US Format and  Second Date UK Date
Then Split the Date column by Delimiter
Then Combine the Dates using this Formula using Custom Column option
if [Format]="USA" then #date( Third Split column ,first split column, second splitcolumn) else
#date(third split column,second split Column, First Split Column)

Use the attached file for idea!

[Anonymous]

This works on small scale. But how would i get the format uk or us for 60.000 rows?

[VijayP]

@Anonymous 

In the Data there should be some general logic which can decide us or uk format, if you could share 2 or 3 rows of teh data from 60000 rows , i can help you! you can share thru email as well

[Anonymous]

Thanks, but i found something

[Anand24]

Hi @Anonymous ,

You can't have different date formats in the same date column in Power BI. A column like this would be text column. What you can probably do is have 2 different date columns with one format each.

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[Anonymous]

This is correct, but how would i do that

[Anand24]

@Anonymous ,

PFA pbix. I have created 2 new columns with one date format each. Date3 and Date4 are the calculated date columns.

Note: The formats you have specified are not available as Date Formats in Power BI. You can have that format but then the field would be text field and not date field. You won't be able to perform date functionalities with those text format.

Below Date formats are available:

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn