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

1 try to delete the automaticly generated column "Changed Type"

2 then click the "fx" button on the left of edit bar, then input these code in the new step edit bar

=Table.PromotoHeaders(Table.Skip(YourPreviousStepName,10))

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.