Forum Discussion
Different date formats in the same column
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
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!
8 Replies
- Anand24
Super User
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- 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
- VijayP
Community Champion
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!
- 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
- 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.
