Use Excel Power Query to Fill Down and Clean CSV Data
Question details
Clean a CSV of grouped purchase-order records by filling down identifiers, removing blank rows, and correcting misaligned values.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A user wants to turn a grouped purchase-order CSV into consistently aligned rows that can be refreshed when a new file is supplied.
- Observed behavior
- Some identifying values appear only on the first row of a group, the CSV contains blank rows, and some cells are misplaced.
Inspect a representative CSV containing several purchase orders and list the columns you need to keep. Identify how misplaced values can be recognized and whether blank identifier cells appear as nulls or empty text.
Clean and Fill Down the CSV in Excel Power Query
Use a refreshable Power Query transformation to clean grouped purchase-order records.
Remove completely blank rows before filling down identifiers; otherwise, filled values can make those rows appear nonblank. Exact alignment rules depend on the CSV layout and a representative sample.
In Excel, select Data > From Text/CSV, choose the CSV file, and select Transform Data to open Power Query Editor.
In Power Query Editor, select Home > Remove Rows > Remove Blank Rows to remove completely blank records before filling down.
Select the identifier columns, such as C, F, and L if those are the correct fields in your file, then select Transform > Fill > Down. Fill Down replaces nulls, so first use Transform > Replace Values if empty text must be converted to null.
Use Add Column > Conditional Column to create corrected values based on a reliable marker in each affected row. Use Transform > Split Column where needed, then select unwanted columns and choose Home > Remove Columns. Drag the remaining column headers into the required order.
Select Home > Close & Load to load the cleaned data into Excel. When the source CSV is updated, select Data > Refresh All to rerun the query.

Open and Review Purchase-Order Data with WPS Spreadsheet
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open CSV and Excel files to review or edit purchase-order data and move between compatible formats. Excel Power Query transformations and refreshes require Excel.

Frequently Asked Questions
Why did Fill Down leave some cells empty?
Power Query Fill Down replaces null values, not empty text. Inspect the affected column and use Transform > Replace Values to convert empty text to null before selecting Transform > Fill > Down.
Should I remove blank rows before or after filling down?
Remove completely blank rows first using Home > Remove Rows > Remove Blank Rows. Filling down first can populate identifier cells in those rows, preventing them from being recognized as blank.
Can Power Query shift misplaced cells like an Excel worksheet?
Power Query does not shift individual cells in place. Instead, use transformations such as Add Column > Conditional Column or Transform > Split Column to build correctly aligned fields based on a consistent rule.
How do I refresh the query with a new CSV?
Update the source file used by the query, then select Data > Refresh All in Excel. If the new file has a different path or structure, update the query's Source step and check that the transformations still apply.
What is needed to create the exact alignment steps?
Prepare a representative CSV containing several purchase orders, a list of columns to retain, and examples showing how to identify misplaced values. Those details determine the conditional transformations.




