Scenario:
We may face the following situation when importing data to Power BI:
From the data source, the whole column is in the right date format.
Once being imported into Power BI, the column will have dates in two formats as below:
Mon 16/11/20 throws an error.
3/15/2021 indicates the correct format.
Through this blog, I will show you how to use Power Query and DAX to convert different date types into unified format.
Table used:
Expected result:
In the Power Query:
- Right click the Date column to split column by delimiter. Then specify the space as the delimiter.
- Right click the Date.2 column and add as new query. Then change the new query to a table. Remove the blank rows.
- Change the type as follows.
- In the original table, change the Date column to Date type, then remove errors and remove Date.2 column.
- Use the Append operation to combine two tables into a single table. Then add column from two columns (Hold Ctrl and select two columns at the same time). The Date column is date type. You can remove the Date.1 and Column1 columns and keep the Date column.
Tips:
For more information about Append operation, please refer to this blog.
DAX Query Operation:
- Create a calculated column. The New Date column is date type.
New Date =
VAR col1 =
IF ( LEN ( [Date] ) > 10, DATEVALUE ( RIGHT ( [Date], 8 ) ) )
VAR col2 =
IF ( LEN ( [Date] ) <= 10, [Date] )
RETURN
IF ( ISBLANK ( col1 ), DATEVALUE ( col2 ), col1 )
Please check the attached files for details.
Author: Stephen Tao
Reviewer: Icey Zhang& Liang Lu
Convert to date type in different formats in the Date column (2).pbix
Be the first to comment