Forum Discussion
Issue with auto detected column from XLS file
From my research and testing, the root cause of the issue is likely that the underlying file is in XLS format rather than XLSX.
If I save it as XLSX, the Power Query import works fine. The problem column is initially identified as type = any, and because no type has been auto detected, the rest of the transformations run successfully.
Apparently the XLS format uses a different connector to XLSX, which is probably why it's behaving differently:
https://learn.microsoft.com/en-us/power-query/connectors/excel#legacy-ace-connector
it's weired，I saw you have closed the function of detecting data type. how the type was changed before the first step?
I assume that's a quirk of the legacy XLS connector - see my reply in the main thread re XLS vs XLSX.