Random date error

[NotEinsten]

Hi

Tried following tips from a bunch of posts in here but failed to solve my problem, therefore this post. I apologize in advance if its a dublet of a former post i failed to find.

My problem is a DataFormat.Error occuring after load or in during transforming of an Excel sheet. Interestingly, the error only happens in a specific column, even though the same symbol occurs in other columns with the same format/data type.

The specific error is caused by a hyphen (-) placed in cells formatted as data type "date". This is directly imported from Excel, which put the hyphen there automatically due to the cells being of "date"-format and no date has been entered (its an end-date, so for tasks not yet completed, there is of course  no date to be put in yet.

I tried following the steps outlined in the posts "https://community.powerbi.com/t5/Desktop/Date-format-error/m-p/130510" and "https://radacad.com/flawless-date-conversion-in-power-query" but without luck.

Does anyone have a tip for me?

Error message after load

Sepcific error message as shown

Error message after tried work-around with changing type using Locale and choosing Danish and Date which is the same as in the source Excel-doc

[NotEinsten]

Hi, thanks for replying

If I format the excel-doc to text the cells containing actual dates get turned into nonsense, so thats not an option. The same goes for converting to "standard"-type.
Below I attached a picture of the part of the excel-doc. The column acting up making error in Power BI is marked with a star. The columns marked with check mark gets imported without any trouble. All three columns are formatted as date with the same typography

[ronrsnfld]

I suspect the hyphen you see is not the result of formatting, but is a real character in the cell.  If you select a cell (in Excel) with a hyphen, and examine the formula bar, do you see the hyphen?  Again, in Excel, what is returned using the formula "=UNICODE(cell_ref)" where cell_ref refers to a cell that has a hyphen?  It may be that your failure to remove the hyphen is because it is not the standard hyphen character.

[mussaenda]

Hi @NotEinsten ,

How about if you do replace error?

I can see your applied steps. Are you loading the excel using folder? or using the excel connector?

[NotEinsten]

Hi and thanks for replying:-)

Im loading it by choosing Excel as import type and then selecting the file. There are other files in the folder. 

Do you mean replace the hyphen with for example 0 or nothing? I tried that, but didnt work.

[mussaenda]

Hi @NotEinsten ,

In which applied step the error is happening?

[NotEinsten]

When closing and importing after tranformation or after pressing "load" after choosing data source to import.

[Waldo35]

Out of pure interest, could u try loading the excel in with the column that's giving an error formatting as text. Purely to see if it loads properly then. Also i can't open either URL to see what steps actually have been followed. 

For tips from now you could also try to format the date column in a different way in the excel. Try it with the / instead of the -.

But you are saying it works with other columns, so maybe try copying it over to a new fresh excel and see if there is difference there when loading the data. the "fresh" start attempt.

[NotEinsten]

I tried playing around with formats, changing locale language/units, loading a fresh version. None worked so far. Also tried cleaning formats and then using format painter from some of the rows with same type of data to ensure correct format. Didnt work either. I circumvented the problem so far by creating a separate excel file with the "problem column", importing it separately and then establish relationship between the data sets in Power BI. Im starting to wonder, if it might have something to do with a capacity issue somewhere in the Office suite. The spreadsheet has more than 1 million rows and 20+ columns.