Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Tips:
For more information about Append operation, please refer to this blog.
DAX Query Operation:
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.