UK dates saved as General or US Date format in excel

[Simon_D]

Hi,

I have sharepoint excel files that contain the following three data entries:

1. 14/10/20 (Text)

2. 22/10/2020 (Text)

3. 14/10/2020 Date is entered in DD/MM/YYYY format but saved in Date English-US locale (MM/DD/YYYY)

On import, dates entered as DD/MM/YYYY which are saved in Date English-US locale transform to MM/DD/YYYY . What would be the best way to clean up the column so that all dates show in UK format?

Appreciate your help.

[PhilipTreacy]

Hi @Simon_D 

if you could supply some sample files that you are importing and the query you are using it would make this easier to figure out.

Phil

[edhans]

Hi @Simon_D - I don't understand your point 3. You said "14/10/2020 Date is entered in DD/MM/YYYY format but saved in Date English-US locale (MM/DD/YYYY)" but I am not clear what that means. 14/20/2020 is not entered as DD/MM/YYYY, and locale settings are not preserved in Excel when importing. It is whatever is in that cell. However, you can try using something like the following in a new Custom Column in Power Query:

each try Date.FromText([Column1], "en-GB") otherwise null

 This converts data from text to the UK English culture dates - DD/MM/YYYY or returns null if it fails. But there are issues with this:

1. Dates like 03/04/2020 will work for both en-US and en-UK, but one is March 4, the other is April 3, and there is no conversion failure.
2. You say this is all in one column - that means either they are all text, or all dates. Even as the dreaded "any" data type, they are interpreted as text, so there are no dates in that column until you do an explict conversion to Date or DateTime.

Can you be a bit clearer with some real data as to what is happening, unless the culture settings shown above will get you what you want?

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Simon_D]

Hi @edhans ,

Thanks for you quick reply!

I am importing excel files from sharepoint - an example of point 3 date entry:

Entered delivery date in excel is 12 October 2020. On import above date updates to 10/12/2020 = 10 December 2020 (see below pic right aligned), while text entries (left aligned) remain unchanged. I wonder why this happens if all my regional device and power bi settings are set to UK and you mentioned that "locale settings are not preserved in Excel when importing"? 

You also mentioned that "any" data type is interpreted as text, but when I try to apply any actions such as split by delimiter, all right aligned entries in the column throw the following error - 

Expression.Error: We cannot convert the value #date(2020, 12, 10) to type Text.

I hope this makes the issue a bit clearer!

[edhans]

Ok, you can use this function to return true/false if it is a date:

Value.Is([YourColumn], type date)

For the dates, it will return true, otherwise false, so perhaps this if/then/else 

if Value.is([Column1], type text) then try Text.FromDate([Column1], "en-GB") otherwise null
else if Value.Is([Column1], type date) then [Column1]
else null

 So if it is text, try and do the en-GB text to date conversion. if that text to date conversion fails, return null. You'd need to give me examples of why it fails (if it does) so we can correct.

If it is not text, then see if it is date. If it is, just get the column.

Otherwise return null.

[Simon_D]

I have noticed that dates are displayed correctly when viewing files in sharepoint but update to incorrect values when downloaded and opened on my machine or imported to power bi. I believe the problem is not specific to power bi and probably does not belong on this forum. 

Thanks for your suggestions guys. 

[edhans]

Interesting. If the files are on SharePoint, they should import into Power BI just fine, as you would see them in Excel if you opened the files - unless they are XLS files, which have known issues with what is shown vs what Power Query really sees. Had a long thread about that issue yesterday - Power Query really needs modern XLSX (or XLSB/M) files, not the old Office 2003 format.

That said, can you share some sample data that we can look at? Via OneDrive share or Dropbox?