How to Prevent Power Query from Converting Text to Null
Question details
The user needs to stop text values in a mixed-type column from becoming null when combining multiple files in Power Query.

- Product
- Power Query / Excel
- Device & OS
- not provided
- Scenario
- Combining multiple files with mixed data types in a single column using Power Query.
- Observed behavior
- Text values in a mixed-type column are converted to null during the navigation or loading step, despite the values existing in the source files.
Ensure you have access to the original source files being combined and verify that all source files use consistent column headers and structures before modifying the query.
Adjust Automatic Data Type Detection in Power Query Editor
Manually set the column data type to Text before Power Query automatically detects types and converts mismatched text values to null.
Power Query usually samples the first 200 rows to determine the data type of a column. If those rows are numeric, it sets the column to a Number type. Any subsequent text values in that column are considered incompatible and converted to null.
To fix this, you need to intercept or modify the automatic 'Changed Type' step applied during the file combining process.
Select your query in Excel, navigate to the 'Data' tab, and click 'Get Data' > 'Launch Power Query Editor'.
Look at the 'Applied Steps' pane on the right side of the window. Find the automatically generated step called 'Changed Type' that occurs right after the Navigation or Expanded Data step.
Click the 'X' next to the 'Changed Type' step to remove it completely, preventing the automatic numeric conversion that causes the nulls.
Click the data type icon (e.g., ABC, 123) in the header of the affected mixed-type column and select 'Text' from the dropdown menu.
Click 'Close & Load' on the Home tab to save your query and apply the changes to your spreadsheet.

Try WPS Office for Seamless Data Management
If you frequently encounter complex type-conversion errors and null value issues in Microsoft Excel's Power Query, consider switching to WPS Office. It provides a highly compatible, lightweight, and intuitive alternative for processing and managing spreadsheet data without the steep learning curve.
- 1. Download WPS Office: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheet and seamlessly open your existing .xlsx or .csv files.
- 3. Process Data Effortlessly: Utilize WPS Spreadsheet's built-in data tools to filter, combine, and organize your mixed-type columns accurately.

Frequently Asked Questions
Why does Power Query convert my text to null?
Power Query samples a preview of the data (usually the first 200 rows) to automatically detect data types. If a column contains numbers at the top but text further down, Power Query assumes the entire column should be numeric. When it reaches the text values, they fail the numeric conversion and are replaced with null.
How do I turn off automatic type detection in Power Query globally?
You can disable this feature by opening Power Query Editor and going to File > Options and settings > Query Options. Under the 'Data Load' section, choose 'Never detect column types and headers for unstructured sources'.
Can I replace existing null values with blank text?
Yes. Select the column with null values, go to the Transform tab, and click 'Replace Values'. Enter 'null' in the 'Value To Find' box and leave the 'Replace With' box blank, then click OK. However, modifying the data type step to prevent the nulls in the first place is the recommended approach.




