Data format error: We couldn't convert to Number. Details: 03/01/1900

[Anonymous]

I've read all relevant posts I've found about it, but none makes sense for my case.

It happens when the report is refreshing in the service, but as well if I refresh manually in power query on the desktop.

Ok, somebody entered 03/01/1900 instead of 03/01/2019 in the source data Excel sheet, but 03/01/1900 is still a valid date for Excel and PowerBI, isn't it? If I change the cell format to Number, it gives me 61.00. Why does this cause the refresh to fail?

Anyway, it's good we spotted that incorrect date and changed it to 2019 in the source file. Still the same error upon refresh.

It's not a mandatory field, so let's try with blank. Still the same!

The query that uses this Excel file loads fine, no errors. It's a different query, where I join that table with another one, that fails.

Table.NestedJoin(#"Source Data", {"XYZ ID"}, #"XYZ Base Data", {"Details No.:"}, "XYZ Base Data", JoinKind.LeftAnti)

Where does it retain that rogue date (which shouldn't be a rogue date anyway) and why does it continue to fail?

[Anonymous]

@Ivancito111 EXCEPT() is not an option, because my tables don't have the same number of columns. The query

Table.NestedJoin(#"Source Data", {"XYZ ID"}, #"XYZ Base Data", {"Details No.:"}, "XYZ Base Data", JoinKind.LeftAnti)

gives me a new table with all the columns in the Source Date file but only for the rows where XYZ ID has no matching entry in XYZ Base Data. This worked well until this 03/01/1900 date issue popped up, and keeps popping up in spite of that there is NO SUCH VALUE in any column of the source data file. Where does this value come from? Is there another way of generating my table?

[Anonymous]

No matter if I set it to another date or to null in the source data file, the nested join operation fails with 03/01/1900 that cannot be converted to to a number. All other dates in this column convert fine and are all of the same format, and now being null in the source data, why can this date still be a problem? I deleted the query and created a new merge query as new, and again the same. 

[Anonymous]

@Syndicate_Admin for some reason the nested join operation needs to convert it into a number. It's perfectly formatted as a date in the Source Data query. I tested with convert using locale, which instead of 61 returns 3, but the query still fails at the same 03/01/1900 date.

[Syndicate_Admin]

Hello good morning.

I am reviewing your requirement, but I have a doubt, because you need to transform it into a number.

Since you can also make the same change through DAX creating a new column and avoiding the update failure.

Best regards.

[Anonymous]

@Syndicate_Admin I don't need a new column, I just need a list of records present in table 1 but not in table 2. Will try the EXCEPT() funcion in DAX instead, thanks for the tip.