Power Query Problems

How to Remove Duplicates and Pivot Exported CSV Data in Excel

Maira MehtabMaira Mehtab Nov 11, 2026 870 views

Question details

The user needs to import a frequently exported CSV file, remove duplicate records, and pivot the values into columns within a separate workbook, while keeping the original source file completely unchanged.

How to Remove Duplicates and Pivot Exported CSV Data in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Automating the cleanup and reshaping of a frequently updated CSV data export to generate clean reports.
Observed behavior
The goal is to establish a repeatable query or formula that outputs a cleaned, pivoted dataset in a separate workbook without altering the raw CSV data.
Before you start

Ensure you know the exact file path of your exported CSV file and verify that its column headers remain consistent across different export instances.

Solution 1Recommended

Use Power Query to Clean and Pivot the Data

Power Query is the most robust method for importing, cleaning, and pivoting data because it creates a repeatable, automated process that can be refreshed with a single click.

This method creates a connection to your CSV file rather than copying the data. Any transformations applied in Power Query are stored as a sequence of steps, leaving your original CSV file completely untouched.

1
Import the CSV File

Open a new blank workbook in Excel. Navigate to the 'Data' tab, click 'Get Data' > 'From File' > 'From Text/CSV', locate your exported CSV, and click 'Import'. When the preview window appears, select 'Transform Data' to open the Power Query Editor.

2
Remove Duplicate Records

In the Power Query Editor, select the column or combination of columns that uniquely identify each record. Right-click the column header and choose 'Remove Duplicates' from the context menu.

3
Pivot the Data

Select the column containing the categories you want to turn into new columns (e.g., industry or category). Go to the 'Transform' tab and click 'Pivot Column'. In the dialog box, select the column that contains your numerical values to populate the new columns, then click 'OK'.

4
Load the Transformed Data

Once the data looks correct, go to the 'Home' tab and click 'Close & Load'. This will output the cleaned and pivoted data into a new worksheet in your Excel workbook.

Use Power Query to Clean and Pivot the Data
One-Click Refresh: When a new CSV file is exported to the same folder path, simply open this workbook, go to the 'Data' tab, and click 'Refresh All'. The data will automatically update applying all your saved steps.
Free Microsoft Office alternative

Easily Manage CSV Data and Pivot Tables with WPS Office

If you are looking for a lightweight, cost-effective way to manage large CSV exports and build powerful Pivot Tables, WPS Office is a highly compatible, free alternative to Microsoft Excel.

  1. 1. Import Your CSV: Open WPS Spreadsheet, go to the 'Data' tab, and select 'Import Data' to securely load your external CSV file into a fresh workbook.
  2. 2. Remove Duplicates: Highlight your imported dataset, navigate to the 'Data' menu, click 'Remove Duplicates', and confirm the unique identifying columns.
  3. 3. Create a PivotTable: Click the 'Insert' tab and choose 'PivotTable'. Drag your category fields to Columns and your value fields to the Values area to instantly pivot the data.
100% compatible with Microsoft Excel formats, including .xlsx, .xls, and .csvRobust PivotTable features to easily reshape, group, and summarize exported dataBuilt-in duplicate removal and intuitive data sorting toolsFree and lightweight application with a familiar, easy-to-use interface
microsoft office alternative - wps office

Frequently Asked Questions

Will removing duplicates in Power Query alter my original CSV file?

No. Power Query only establishes a one-way connection to the original CSV file. All transformations, including removing duplicates and pivoting, are applied virtually and outputted to a new Excel worksheet, leaving your source file completely untouched.

How do I update my report when a new CSV is exported?

If you set up the process using Power Query, simply overwrite the old CSV file in your computer's directory with the newly exported one (ensure it has the exact same file name and location). Then, open your Excel workbook, go to the 'Data' tab, and click 'Refresh All'.

Why is the PIVOTBY formula returning a #NAME? error?

The PIVOTBY function is a modern dynamic array function exclusively available to users on the latest versions of Microsoft 365. If you are using an older version of Excel (like Excel 2019 or 2021), the function will not be recognized. In this case, use the Power Query method instead.

Can I automate this entire CSV cleaning process without using VBA macros?

Yes. Power Query is built specifically for macro-free automation. It records every click and data transformation step as a query. Once set up, repeating the entire cleaning and reshaping process only requires clicking the Refresh button.