Does Excel.Workbook() read the schema of the Excel file ?

[WanderingBI]

Dear all,

On the internet I find the information that Power Query will not read the table schema of an imported Excel file when using Excel.Workbook() as an Excel file is unstructured data.

https://support.microsoft.com/en-gb/office/add-or-change-data-types-power-query 

In Power BI I activated the option -> Global - Data Load -> Type Detection: Never detect column types and headers for unstructured sources.

I used the following Power Query code to import the file:

let
    Source = Excel.Workbook(File.Contents(filepath), true),
    Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
in
    Sheet

I can see that data types are applied to the columns in the table (number, date, ...)

So my question is if Power Query is automatically detecting the file types according to the values in the table when evaluating Excel.Workbook() or is it reading the types from an Excel schema?

[BA_Pete · Accepted solution]

Hi @WanderingBI ,

It's detecting the data types from the first 1,000 row values as default.

I'm unsure on exactly the mechanics it uses i.e. whether it prioritises the earlier row values, or whether it uses the most frequent type over the entire 1,000 rows, but this is the basic method.

Pete