Power Query Problems

How to Fix Excel PivotTable Date Columns Treated as Text from SSAS

Olivia MillerOlivia Miller Nov 10, 2026 869 views

Question details

The user needs to resolve an issue where date fields imported into an Excel PivotTable from an SSAS Tabular model are interpreted as text.

How to Fix Excel PivotTable Date Columns Treated as Text from SSAS
Product
Excel
Device & OS
not provided
Scenario
Importing data from an SSAS Tabular model into an Excel PivotTable through Analysis Services.
Observed behavior
Date columns in the resulting PivotTable are treated and formatted as text, which prevents correct date filtering, grouping, and chronological sorting.
Before you start

Ensure you have access to the original SSAS Tabular model data connection in your workbook and that the Power Query Editor is available in your Excel version.

Solution 1Recommended

Define Date Data Types Explicitly in Power Query

Setting the correct data type for every relevant column in Power Query prevents the Excel data model from defaulting unspecified fields to text.

When importing data from external sources like SSAS Tabular models, it is a best practice to ensure all fields have strictly defined data types before the final load step. Fields left with an 'Any' or unspecified data type are often interpreted as text by the data model, which removes your ability to use PivotTable date features natively. Defining types directly in Power Query also improves filtering operations and query performance.

1
Open the Power Query Editor

In your Excel workbook, go to the 'Data' tab on the ribbon, click 'Queries & Connections', and double-click your specific SSAS Tabular data source to open the Power Query window.

2
Locate and Select Date Columns

Scroll through the data preview window to find the specific date columns that are currently behaving as text in your PivotTable.

3
Change Data Type to Date

Click the small data type icon situated on the left side of the targeted column header. From the dropdown menu, select 'Date' or 'Date/Time' to explicitly define the format.

4
Close and Load Data

Click the 'Close & Load' button in the upper-left corner of the Home tab. This applies the corrected data types to the data model and refreshes your PivotTable.

Define Date Data Types Explicitly in Power Query
Performance Improvement: Assigning correct data types in Power Query not only fixes your formatting but can also significantly improve data model performance and PivotTable responsiveness.
Free Microsoft Office alternative

Looking for a Simpler Data Analysis Tool?

If managing external data models and Power Query configurations in Microsoft Excel feels overly complex, consider switching to WPS Office. It provides a lightweight, highly compatible spreadsheet application perfect for seamless data analysis and PivotTable generation.

  1. 1. Download WPS Office: Visit the official WPS website to download and install the free suite.
  2. 2. Open Your Spreadsheet: Launch WPS Spreadsheet and easily open your existing .xlsx workbooks.
  3. 3. Analyze Data Smoothly: Select your data range and click 'Insert' > 'PivotTable' to begin analyzing your data without complex external configurations.
Seamlessly open, edit, and save Microsoft Excel (.xlsx) formats.Lightweight design ensures fast performance even on older devices.Built-in PivotTable tools for straightforward data analysis.Familiar user interface makes switching completely effortless.
QA img-9

Frequently Asked Questions

Why does Excel treat my imported SSAS Tabular dates as text?

If a date column is imported without a specifically defined data type, Excel's data model may interpret the 'Any' or 'Unspecified' type as generic text. Explicitly assigning a Date data type in Power Query prevents this issue.

Does correcting the data type in Excel alter the original SSAS model?

No. Changing the data type in Power Query only shapes how the data is read and stored inside your specific Excel workbook. The original SQL Server Analysis Services database remains untouched.

Can incorrect data types affect my PivotTable performance?

Yes. Leaving columns with unspecified data types forces the data model to continually evaluate the data format, which can slow down query load times, filtering operations, and overall PivotTable calculation speeds.