How to Use Power Query Instead of PivotTables to Preserve Original Excel Data
Question details
The user needs to display original text data entries in Excel without them being automatically summarized or aggregated, which happens by default when using PivotTables.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Organizing, reshaping, or displaying raw data that contains specific text entries (such as M4, IV, ++) without performing mathematical aggregations.
- Observed behavior
- PivotTables automatically summarize and calculate values by design, failing to retain and display the original unaggregated text strings the user wishes to see.
Ensure your Excel version supports Power Query (also known as Get & Transform Data in newer versions) and verify that your source data is organized in a clean, tabular format before importing.
Import and Reshape Data using Power Query
Use Excel's Get & Transform feature to import your data and structure it exactly as it appears in the source, avoiding the automatic aggregation triggered by PivotTables.
PivotTables are designed primarily for calculating and summarizing numerical data. When you place text fields into the Values area of a PivotTable, Excel forces a 'Count' aggregation, which prevents you from seeing the raw text.
Power Query solves this by allowing you to query, shape, and load the data directly into a new worksheet table exactly as it is. It is highly effective for combining and filtering original data without summarizing it.
Go to the 'Data' tab on the Excel ribbon. Click on 'Get Data' (or 'From Table/Range' if your source data is already selected in the current workbook) and choose the appropriate data source.
Once the data is loaded into the Power Query Editor window, you will be able to view all your raw records and original text entries without any automatic summarization.
Use the built-in editor options to remove unwanted columns, filter specific rows, or reorder the data structure to fit your presentation needs.
Click the 'Close & Load' button located on the Home tab of the Power Query Editor. Choose 'Close & Load To...' and select 'Table' to output the preserved unaggregated data onto a new or existing worksheet.
Switch to WPS Office for Seamless Data Management
If you frequently struggle with complex Excel features like Power Query or need a lightweight, free solution for everyday data processing, WPS Office provides an intuitive spreadsheet application. It is highly compatible with Microsoft Excel formats and offers familiar filtering and formatting tools without the steep learning curve.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your device.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheets and seamlessly open your existing Microsoft Excel files without losing any formatting.
- 3. Manage Data Easily: Use the familiar Data tab to sort, filter, and extract your original text entries without needing complex query setups.
Frequently Asked Questions
Why does my PivotTable show 'Count of' instead of the actual text?
PivotTables are fundamentally built to aggregate data. When you drag a text field into the Values area, Excel cannot mathematically sum the text, so it defaults to counting the number of occurrences. To show actual text fields side-by-side, you must use Power Query or complex DAX formulas in Power Pivot.
Can I refresh Power Query data just like a PivotTable?
Yes, Power Query maintains a live connection to your original source data. You can refresh the output table by right-clicking it and selecting 'Refresh', or by clicking 'Refresh All' on the Data tab.
Is Power Query available in older versions of Excel?
Power Query is natively integrated under the 'Data' tab as 'Get & Transform' in Excel 2016 and newer versions. For Excel 2010 and 2013, it is available but must be downloaded and installed as a separate free add-in from Microsoft's website.
