Fix Excel Power Query Omitting Text Values from a Data Column
Question details
The user needs to stop Excel Power Query from converting a column to a numeric type when later rows contain text values, which currently makes text values appear as errors or nulls.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The user imports external data into Excel with Power Query. A column contains numbers in the first 200 rows and text values later, so Power Query infers the whole column as numeric.
- Observed behavior
- Text values after the first 200 rows are omitted, shown as errors, or returned as null because they do not match the inferred numeric data type.
Open the workbook and display the Queries & Connections pane from Data > Queries & Connections so you can identify the query with errors and locate the type-conversion step in the Applied Steps list.
Change the Power Query column type from number to type any
Use this when a column contains a mix of numbers and text and the automatic type detection has assigned a numeric type, causing text rows to become errors or nulls.
Power Query infers data types from the first 200 rows by default. If those rows contain only numbers, later text values do not match the inferred numeric type and are replaced by errors or nulls. Changing the column to type any allows the column to keep both numbers and text values.
In Excel, go to Data > Queries & Connections to open the pane and check for queries showing an error count or warning.
Right-click the affected query in the Queries & Connections pane and choose Edit to open the Power Query Editor.
In the Applied Steps list on the right, select the step that changed the column type, usually named Changed Type.
In the formula bar, replace the numeric type with type any for the affected column. For example: Table.TransformColumnTypes(Source, {{"Data", type any}}). Replace Data with the actual column name.
Click File > Close & Load to return the corrected query results to Excel and verify that the text values now appear.
Prevent automatic type detection when importing the data
Use this if you want to avoid the inferred Changed Type step from the start and keep the original mixed values during import.
Work with Excel Data Without Power Query Headaches in WPS Office
WPS Office is a free, lightweight suite that opens, edits, and saves Excel workbooks with a familiar ribbon interface. It preserves .xlsx, .xls, and .csv compatibility, so you can view and clean mixed text and number data without paying for Microsoft Office.
- 1. Download WPS Office: Go to the WPS Office download page and install the free Office suite on your computer.
- 2. Open your Excel workbook: Launch WPS Spreadsheets and open the .xlsx or .xls file that contains the data you need to inspect.
- 3. Review the data column: Use familiar spreadsheet tools to locate the column and confirm whether text values are displayed correctly.
Frequently Asked Questions
Why does Power Query change text values to null in Excel?
Power Query detects a column data type from the first 200 rows by default. If those rows contain numbers, it can assign a numeric type, and later text values that do not match become errors or null results.
How do I find which Power Query has type errors?
Open Data > Queries & Connections in Excel. The pane shows an error count next to queries that contain rows failing the current data type conversion.
Can a Power Query column contain both numbers and text?
Yes. In the Power Query Editor, change the column type to Any or Text. For example, use Table.TransformColumnTypes(Source, {{"Data", type any}}) and replace Data with your actual column name.
Will changing a column to type any break numeric calculations?
Type any keeps mixed values, but you should create a separate numeric column or conversion for calculations instead of relying on the original mixed column for math.
Does WPS Office include Power Query?
WPS Office is a free Office alternative that opens and edits Excel files, but Power Query tasks remain a Microsoft Excel feature. You can review and clean mixed text and number data with WPS Spreadsheets.
