OK after so many hours and days fighting date issues, I finally figured out a way to deal with this new flavor:
Symptom:
When loaded into Power BI, all values becomes a number (which is exactly how Excel stores dates, btw), and you cannot convert them to date. Each try gives you the same error message.
So I first make sure it is indeed date format in Excel, and you ar promoting the first row to head, everything is checked, and still doesn't work out. So frustrated I searched everywhere and tried all regions from English(US) to some remote countries I barely heard about, and the error sustains.
Solution:
Close the Excel file
Yes, that simple. I don't even need to save and close because I already saved it hours ago, so just close it. I figured it might has something to do how Excel stores the filebut I'm not sure. Apparently Power BI reads the dates as numerical values, which is indeed how Excel stored dates, maybe Power BI doesn't use the same numerical formulas, no?
