Power Query Problems

How to Prevent Power Query from Converting Text to Null

Muhammad TalhaMuhammad Talha Oct 14, 2026 871 views

Question details

The user needs to stop text values in a mixed-type column from becoming null when combining multiple files in Power Query.

How to Prevent Power Query from Converting Text Values to Null
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.
Before you start

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.

Solution 1Recommended

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.

1
Open Power Query Editor

Select your query in Excel, navigate to the 'Data' tab, and click 'Get Data' > 'Launch Power Query Editor'.

2
Locate the Changed Type Step

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.

3
Remove or Edit the Automatic Step

Click the 'X' next to the 'Changed Type' step to remove it completely, preventing the automatic numeric conversion that causes the nulls.

4
Manually Set Column to Text

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.

5
Close and Load

Click 'Close & Load' on the Home tab to save your query and apply the changes to your spreadsheet.

Adjust Automatic Data Type Detection in Power Query Editor
Data Preserved: By enforcing the Text data type early in the applied steps, Power Query will read both numbers and text as text strings, fully preventing them from becoming null.
Free Microsoft Office alternative

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. 1. Download WPS Office: Visit the official WPS Office website and download the free installation package for your operating system.
  2. 2. Open Your Spreadsheets: Launch WPS Spreadsheet and seamlessly open your existing .xlsx or .csv files.
  3. 3. Process Data Effortlessly: Utilize WPS Spreadsheet's built-in data tools to filter, combine, and organize your mixed-type columns accurately.
Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsFree and lightweight Office suite with reliable data processing toolsFamiliar and intuitive user interface for seamless migrationEasily combine and manage data sets without unexpected formatting errors
microsoft office alternative - wps office

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.