Power Query Problems

How to Clean and Normalize Messy CSV Data with Power Query

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to clean and structure a messy CSV file exported from a point-of-sale system, which contains shifting headers and blank columns, making it hard to analyze.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing a poorly structured PDF-to-CSV report from a point-of-sale system into a spreadsheet for structured analysis.
Observed behavior: The data has shifting columns, changing header positions, and blank columns, which prevents standard Power Query imports from aligning the data correctly by default.

Before you start

Ensure you have saved a backup copy of your original CSV file before importing and modifying it in Power Query.

Solution 1Recommended

Split Data into Sections and Promote Headers in Power Query

Use Power Query to divide the data into manageable sections based on repeating headers, convert them into a list of tables, and then promote and expand them to align the data.

When PDF-to-CSV conversions cause columns and headers to shift, standard flat-file imports fail to align the data. By splitting the dataset into sections based on a recurring header (like 'Description'), you can process each section individually before merging them back together.

Import the CSV File

Open Excel, go to the 'Data' tab, click 'From Text/CSV', select your messy CSV file, and click 'Transform Data' to open the Power Query Editor.

Identify the Repeating Header

Locate a recurring header in your dataset, such as 'Description', which consistently marks the beginning of a new section in the messy report.

Split into a List of Tables

Add a conditional column or use standard transformations to group and split the data into separate sections based on that repeating header, effectively creating a list of tables.

Promote Headers for Each Table

Apply the 'Use First Row as Headers' transformation specifically to each table within your list so that the local headers are properly assigned.

Expand the Tables

Click the expand icon on the table list column. Power Query will now match and align the data across all tables by their promoted column headings, ignoring the original messy column positions.

Alignment Complete: Once expanded, blank columns and shifted data will be properly normalized into a structured table ready for analysis.

Free Microsoft Office alternative

Easily Manage CSV and Spreadsheet Data with WPS Office

While Power Query is a powerful tool for complex data transformations, WPS Office provides a lightweight, highly compatible, and free alternative for standard spreadsheet management, CSV imports, and data analysis. Enjoy a familiar interface without the hefty subscription fees.

1. Download and Install: Get WPS Office from the official website and install it on your device.
2. Open WPS Spreadsheets: Launch the application and select 'Spreadsheets' from the main menu.
3. Import CSV Data: Go to Menu > Open, select your CSV file, and use the text import wizard to properly delimit and clean your data.

High compatibility with Microsoft Excel formats including .xlsx, .xls, and .csv files.Powerful built-in data tools like Text-to-Columns and Pivot Tables for rapid data cleanup.Lightweight installation and smooth performance even on older devices.Familiar user interface that requires zero learning curve to migrate.

microsoft office alternative - wps office

Frequently Asked Questions

Why do CSV files from PDF exports often have misaligned columns?

PDFs are primarily designed for visual layout and printing, not structured data. When converted to CSV, the extraction software often misinterprets spacing, resulting in shifting headers, merged fields, and unexpected blank columns.

How does promoting headers help align data in Power Query?

When you promote headers for individual tables before expanding them, Power Query uses those specific column names to match and align the data across all tables, entirely ignoring their original, messy column positions.

Can I automate this Power Query cleanup for future messy reports?

Yes. Once you apply these transformation steps in Power Query, they are saved as a query. You can simply overwrite the source CSV file with a new one and click 'Refresh All' in the Data tab to apply the exact same cleanup steps automatically.

Excel & Data Problems