Forum Discussion

tom_brennan's avatar
tom_brennan
Frequent Visitor
2 years ago
Solved

Issue with auto detected column from XLS file

I am trying to import data using Power Query for Excel, and running into a data type inference issue around date/time.   I have an XLS (not XLSX) file from our service provider which I am trying to...
Need Help
  • tom_brennan's avatar
    tom_brennan
    2 years ago

    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

     

     

     

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
2 years ago
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
    Custom1

try this code, copy it to your advanced editor to replace all your code.

tom_brennan's avatar
tom_brennan
Frequent Visitor
2 years ago

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

 

 

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

     it's weired，I saw you have closed the function of detecting data type. how the type was changed before the first step?

    • tom_brennan's avatar
      tom_brennan
      Frequent Visitor
      2 years ago

      I assume that's a quirk of the legacy XLS connector - see my reply in the main thread re XLS vs XLSX.