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!

[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.

[GeraldGEmerick]

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