Power Query Problems

Use Excel Power Query to Fill Down and Clean CSV Data

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 870 views

Question details

Clean a CSV of grouped purchase-order records by filling down identifiers, removing blank rows, and correcting misaligned values.

Use Excel Power Query to Fill Down and Clean CSV Data
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.
Before you start

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.

Solution 1Recommended

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.

1
Import the CSV

In Excel, select Data > From Text/CSV, choose the CSV file, and select Transform Data to open Power Query Editor.

2
Remove blank rows

In Power Query Editor, select Home > Remove Rows > Remove Blank Rows to remove completely blank records before filling down.

3
Fill down identifiers

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.

4
Correct alignment and select columns

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.

5
Load and refresh the result

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.

Clean and Fill Down the CSV in Excel Power Query
Check group boundaries and alignment rules: Fill Down carries the previous value into null cells, so verify that it does not carry an identifier into the wrong purchase-order group. Power Query does not shift individual cells like a worksheet; row-specific corrections require rules based on the actual data.
Free Microsoft Office alternative

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.

Review and edit purchase-order CSV data in a spreadsheetOpen and save compatible Microsoft Excel formats, including XLSXUse a familiar interface for an easier move from Microsoft OfficeWork with a free, lightweight office suite
QA img-9

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.