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

     

     

     

tom_brennan's avatar
tom_brennan
Frequent Visitor
2 years ago

I'm not sure what you mean re step (1). As per the screenshot, there is no automatically generated column "Changed Type". There is Column4, but I need to retain that column.

 

Re step (2), that certainly removes the 10 heading rows, but the type inference has already happened in the previous step ("Navigation").

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
2 years ago

could you copy your hard code from  Advanced Editor?

  • tom_brennan's avatar
    tom_brennan
    Frequent Visitor
    2 years ago

    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"

     

    • 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