Convert text to date - Inconsistency

[Anonymous]

Hi, hoping someone might be able to shed some light on the following.

I've connected to an Online SharePoint list that has just over 19000 entries.

When viewing the data in PowerBI i noticed date columns were in text so i convered them into Date format, e.g. Created column. In the SharePoint list all date columns are in UK format (dd/mm/yyyy)

I selected convert to dd/MM/yyyy format. However, i noticed for some data entries the dd and MM have switched. E.g. I have entries that were created on 12/03/2018 appearing as  03/12/2018 (which is not possible given were only in April). 

When convrting from text to to date format there is a prompt stating some issues can occur. I was wondering if this was a result of the large number of data entries?

Has anyone else experienced this?

If so, were you able to fix.

Any advice is greatly appreciated.

[Greg_Deckler]

Perhaps you could do a Split on the column and then create a new column using the #date constructor to ensure that you construct the dates correctly?

https://msdn.microsoft.com/en-us/query-bi/m/sharpdate

[Anonymous · Accepted solution]

Thanks for the message and idea!

I ended up using the extract before deliminator function to just extract the date and then re-ran Detect Data Type. Thankfully this worked!