Forum Discussion
Date Format [DDMMYYYY] Not Detecting Properly
I've seen similar topics posted on this forum, but haven't been able to create a working solution. What Im lookin for is to import data in its [DDMMYYYY Time] format and have PBI read it as a correct...
I did the following in the Power Query Editor:
= Table.AddColumn(#"YourPreviousStep", "DateToDate", each Text.Start([Date], 2) & "." & Text.Middle([Date], 2, 2) & "." & Text.End([Date], 4), type text)
Once done, I chose "Using Locale..." in data type selection:
Where I selected English (United Kingdom), and as a result, got all the dates in date format.
After that I deleted the original Date column and renamed the new column with the original name and even moved the column to its original place.
Even quicker way is to make the following in the Power Query Editor:
= Table.AddColumn(#"YourPreviousStep", "DateToFormat", each Text.End([Date], 4) & Text.Middle([Date], 2, 2) & Text.Start([Date], 2), type text)And once there, just change the type to Date. But also with this there is the need of replacing the original column.
Once one of the above is applied, it anyway looks like the original date column in the UI.
Would be interesting to know if there is already a better way to do this.