Date Format [DDMMYYYY] Not Detecting Properly

[Anonymous]

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 date.

I have data imported that follows the format [DDMMYYYY Time] - PowerBi Desktop does not seem to recognize it as a date. I have done what others have suggested and used the "locale" to set Date/Time as English (UK). When I do that, dates that would normally say "error" change to the format (e.g. 25072019 changes to 07252019) but other dates that read as correct in that format stay the same (e.g. 07042019 is still 07042019),creating some wrong/some right dates. I have tried setting my PBI desktop option to be English (UK) and set the data to automatically detect fields as suggested in other posts.

I would prefer not to have a calculated field cause other people may work with the report and be confused by multiple date columns, but if it's that only solution then I'm fine with it.  Any help is appreciated!

[Theiren]

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.

[VijayP]

In the Power Query Edit ,Try to change the Data type using Locale. for idea see the screeshots. This approach should help you,

[Anonymous]

Thanks for the response, but that didn't solve my problem. Even setting to English (India) is still interprets 2/7/2019 as February 7, 2019. No matter what location I set it to it reads it that way even though that date is meant to be July 2, 2019.

[VijayP]

Then Select English US . It can be resolved else share the few rows of Date Field so that I will creat video for you!