Convert to date type in different formats in the "...

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:

Table used.png

Expected result:

Table used2.png

In the Power Query:

Right click the Date column to split column by delimiter. Then specify the space as the delimiter.

IN the PQ1.png

In the PQ2.png

In the PQ3.png

Right click the Date.2 column and add as new query. Then change the new query to a table. Remove the blank rows.

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.