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
could you copy your hard code from Advanced Editor?
See below (filename has been obscured for obvious reasons)
let
Source = Excel.Workbook(File.Contents("C:\path\to\file\PowerQueryDateLoadIssue.xls"), null, true),
#"MV Composition1" = Source{[Name="MV Composition"]}[Data]
in
#"MV Composition1"
let Source = Excel.Workbook(File.Contents("C:\path\to\file\PowerQueryDateLoadIssue.xls"), null, true), #"MV Composition1" = Source{[Name="MV Composition"]}[Data], Custom1=Table.PromoteHeaders(Table.Skip(#"MV Composition1",10)) in Custom1try this code, copy it to your advanced editor to replace all your code.
That's what I tried in my first reply when you suggested it.
See results below. The type inference happens in the step before Custom1, so any removal of rows or promotion of headers is not getting around the changed type
it's weired，I saw you have closed the function of detecting data type. how the type was changed before the first step?