Issue with auto detected column from XLS file

[tom_brennan]

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 transform with Power Query in Excel.

The file has 10 rows of heading data, before the row I actually want to use as headers. So the ultimate intention is to remove the first 10 rows, and then promote the next row to headers.

However before I even get to that part of the transform process, some of the raw data has been corrupted. The issue is that column 4 has a couple of rows in the heading section (which I don't care about!) with dates/times in them, and the auto-detect functionality incorrectly tries to interpret the entire column as Date/Time - which largely results in errors.

Below are the raw data, and how Power Query tries to infer it:

The only steps in the Applied Steps section are Source and Navigation. There are no data type changes. So while I can add a Change Type step, it has no effect as the data as already been incorrectly interpreted. There is no way to insert a Change Type step before the data type gets detected.

I have tried turning off type detection for unstructured sources at both a global and workbook level, but these seem to have no effect.

The only way I have been able to get the data into Power Query successfully is to manually modify the source file before loading, which partly defeats the purpose of using Power Query!

Can anyone suggest a workaround?

[tom_brennan · Accepted solution]

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]

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]

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]

could you copy your hard code from  Advanced Editor?

[tom_brennan]

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]

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]

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]

 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]

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