Power Query Problems

Fix Power Pivot Data Types Reverting to Text After Refresh in Excel

Algirdas Jasaitis Oct 16, 2026 868 views

Question details

The user needs to prevent Excel Power Pivot column data types from automatically reverting to Text whenever the data is refreshed.

How to Fix Power Pivot Data Types Reverting to Text After Refresh

Product: Excel
Device & OS: not provided
Scenario: Refreshing data models in Power Pivot.
Observed behavior: Columns manually assigned a specific data type in Power Pivot revert to Text after a refresh, even when tables appear blank or lack external data connections.

Before you start

Ensure you have access to the original data source and the Power Query Editor, as data type modifications made directly in Power Pivot may be overwritten by the underlying query during a refresh.

Solution 1Recommended

Set Data Types in Power Query Editor Before Loading

The most reliable way to enforce data types in Power Pivot is to define them at the Power Query level before the data is loaded into the Data Model.

When data is imported into Power Pivot, it inherits the schema defined by the connection. Modifying the data type in Power Query ensures that the correct formatting is permanently passed to the Data Model during every refresh.

Open Power Query Editor

Open your Excel workbook, navigate to the 'Data' tab on the ribbon, and click on 'Queries & Connections'. Double-click the relevant query to launch the Power Query Editor.

Change Data Type

In the Power Query Editor, locate the column that keeps reverting to Text. Click the data type icon on the left side of the column header (e.g., 'ABC' for Text) and choose your desired data type (e.g., 'Whole Number', 'Date', or 'Decimal Number').

Save and Load

Click the 'Close & Load' button in the top-left corner of the Home tab to save your transformations and apply them directly to the Power Pivot Data Model.

Set Data Types in Power Query Editor Before Loading

Permanent Fix: Defining data types in Power Query ensures that subsequent refreshes inherit the correct schema without resetting to default text formats.

Solution 2

Review Import Steps for Conflicting Transformations

If the issue persists, another transformation step within your query might be overriding your settings and resetting the column types back to text.

Free Microsoft Office alternative

Looking for a Lighter, Faster Spreadsheet Tool?

If you frequently struggle with complex Excel data models, connection errors, or bloated workbook sizes, consider WPS Office. It provides a lightweight, highly compatible alternative for standard data analysis, pivot tables, and daily spreadsheet tasks without the steep learning curve.

1. Download and Install: Visit the official WPS Office website to download the free installation package and install the software.
2. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing Excel workbooks directly.
3. Analyze Data Easily: Use built-in Pivot Tables and charting tools to analyze your data smoothly without complex underlying data models.

Highly compatible with Microsoft Excel formats (.xlsx, .csv, .xls).Robust standard Pivot Table features for quick and easy data analysis.Free, lightweight, and uses minimal system resources.Familiar, tabbed user interface for a seamless transition from Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my Power Pivot column data types change automatically?

When you refresh data in Power Pivot, it pulls the schema and data from the underlying source or Power Query connection. If the data type was only changed manually inside Power Pivot, the refresh overwrites it with the source's default text format.

Does Power Query Editor override Power Pivot settings?

Yes. Power Query acts as the data preparation layer before data enters the Power Pivot Data Model. Any transformations or data type assignments made in Power Query will dictate the format in Power Pivot after a refresh occurs.

How do I check if my table has an external data connection?

In Excel, go to the 'Data' tab and click 'Queries & Connections'. This pane will list all active connections and queries linked to your workbook, even if the tables appear empty on the spreadsheet.

Excel & Data Problems