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.
Hello,
I have a column of dates imported from an Excel sheet into Power Query within Power BI. The column has dates in two different formats: dd.mm.yyyy and dd/mm/yyyy. When changing the column type from text to date, it only recognizes the dates in the format dd/mm/yyyy but for the dates in the format dd.mm.yyyy it just shows error. How to convert all the dates in the correct format dd/mm/yyyy without errors? Any help is much appreciated!
Changing type to Date:
Source data from Excel:
Day |
03.01.2021 |
03.01.2021 |
04.01.2021 |
04/01/2021 |
04/01/2021 |
04/01/2021 |
05/01/2021 |
05/01/2021 |
08/01/2021 |
08/01/2021 |
08/01/2021 |
08/01/2021 |
08/01/2021 |
08/01/2021 |
10/01/2021 |
10/01/2021 |
Solved! Go to Solution.
I changed the Date - Locale - English (UK) for the column in PQ but it didn't work. However, i changed the Regional settings and it worked!
if your default date format of PQ is dd/mm/yyyy then it will automatically give the right output when you selecte Date data type.
Otherwise, you can use Locale English(UK) to convert this into dd/mm/yyyy column. You will need to click ABC123 in your column heading - Using locale - Data type, select Date - Locale - English (UK)
If above doesn't work for you, replace . with / by selecting column - Transform - Replace values - Value to find will be . and Replace with will be /
Then you can convert the column to Date.
OR
You can use following formula in a custom column
=Date.From([Day])
OR
=try Date.From(Text.Replace([Day],".","/")) otherwise Date.From([Day])
I changed the Date - Locale - English (UK) for the column in PQ but it didn't work. However, i changed the Regional settings and it worked!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.