Unable to stop auto-detect of data types despite turning off the option

[Splatz]

Hi all,

I am working with PowerQuery in Excel (not BI but should be the same and couldn't see an Excel PQE forum) and consolidating data from Excel files held in a SharePoint folder. The consolidation works fine apart from the fact that I am unable to turn off the auto detection of data type. I have unticked the auto detect option in both the global and workbook options. Also there is no ' Changed Type' code to remove from the script.

Why this causes an issue:

The source data is formatted to show 000's and so data in the columns which PQE believes to be text do not read correctly e.g. 1,234 appears in the formatted source table as 1 and reads into PQE as 1.

What causes the issue?

The 'header' in the range of the source data seems to be the cause of the issue. If the header is text then the data type is set to text, if a number then the data type is set to be numerical. Note the data is a range with c40 columns, the first two containing text labels the rest numerical values (all values apart from the header and 6 blank rows above the range), c30 rows. So, for example, I have column headers for periods numbered 1 to 12 followed by 'Year'. Data in the columns 1 to 12 are read correctly as the data type is detected as numerical, data from the 'Year' column is read as text. I also tried changing all headers to text and then all values were read as text. Note the source data is a range rather than an Excel table.

Please note that while I understand it is far easier to be able to review actual data, screenshots etc I am unable to provide these due to company/industry security restrictions. I know that I can work around, say by having an additional feeder table in the source, but wondered if anyone has a solution to simply turn off the ******* auto-detect. Thanks!

[Murtaza_Ghafoor]

Disable automatic detect column types and header settings and when loaded your file then manual select the columns types. 

[Royel]

Hi @Splatz  to get rid of the data type auto-detect issue, we have two options

1. Disable Automatic Type Detection

   Turn off the auto-detection feature entirely to prevent Power Query from making assumptions about your data:

   • Navigate to: File → Options and Settings → Options → Data Load → Type Detection
   • Uncheck "Detect column types and headers for unstructured sources."
   • This setting is available for both Current Workbook (local) and Global (all files)

2. Manual Type Assignment (Best Practice)

   Rather than relying on automatic detection, take control of your data types:

   • Review each column in your dataset after the initial load
   • Explicitly set the appropriate data type for each column, even if Power Query has already assigned one
   • This ensures accuracy and prevents unexpected data conversions or meshup errors. 
     

     
     
     Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

     I’d love to stay connected. Join me on LinkedIn for more tips, learning paths, and real-world Fabric & Power BI solutions.

[ronrsnfld]

Perhaps you can make up a sample data table (made up data) that demonstrates the problem but does not reveal company secrets and provide that and the M-Code that produces your problem.

I tried to set up something similar to what you describe, and could not reproduce your problem. There was no #"Changed Type" and all of the columns had the symbol for "type any"

[GeraldGEmerick]

@Splatz I tried this in Power BI Desktop and I don't see the same behavior you are seeing so perhaps it is just a problem in Excel. In Power BI, I turned off all the autodetect settings and used the following data:

Year	1	2	3	4	5
2024	1,234	30,000	4,000	4,000	3,333
2025	1,234	30,000	4,000	4,000	3,333

As you can see below, it did not add a change type step and the columns are all "ABC123". That said, they do actually appear to be numbers (right justified) but there was no loss of what comes after the commas. This is on a US build/settings of Power BI desktop, not sure if that makes a difference.

[Splatz]

Thanks @GeraldGEmerick. I think it may relate to me using the 'SharePoint Folder' as the data source. I believe this source may be relatively new so perhaps works a little differently to using the URL via the 'Web' source. 
I did simplify the problem query using the 'SharePoint Folder' source as follows (specifics renamed) to read just a single file (original acts to consolidate multiple files):

This still converted all the column data types, this time converting all to Text despite some columns only containing null or numerical values. I'll try using the 'Web' source to see if I still have the same issue and report back.

[Splatz]

OK, I think I found the problem. I saved the same sample data file as both  .xlsb and  .xlsx.

The columns in the .xlsb file had the column data types auto-detected (incorrectly), the columns in the .xlsx did not allowing me to set the types manually.

To confirm the settings:

So, I don't know why this happens - I expect it relates to the structure of binary files (that I probably won't understand) but at least I have a simple solution, being to save as .xlsx

[GeraldGEmerick]

@Splatz Really interesting!

[GeraldGEmerick]

@Splatz Can you provide mock/sample data that will recreate this?