Power Query saves your transformations as reusable steps that run again whenever you refresh the query. Because it remembers everything you do, future imports with the same layout can be updated by simply clicking Data > Refresh All. Excel then automatically reapplies all your steps, eliminating repetitive CSV cleanup from your workflow.

Step 1: Import your CSV files into Excel the smart way

Starting with the right import process

To clean up your files properly, you need to import them through Excel’s data tools instead of simply double-clicking the CSV file.

Here’s the workflow:

1. Open a blank workbook and go to the Data tab.
2. Click Get Data > From File > From Text/CSV.
3. Locate and select the CSV file, then click Import.
4. Review the preview window to make sure Excel has correctly detected the delimiter (usually a comma). If the data is not properly split into columns, change the delimiter.

Click Transform Data.

This opens the Power Query Editor workspace where all cleaning happens before the data reaches Excel.

Step 2: Clean up CSV formatting with a few clicks

Scrubbing the junk

Once the Power Query Editor opens, you can start fixing the issues that usually make raw CSV files difficult to work with.

Here are some of the most useful cleanup actions you can perform:

Split full names

1. Right-click the Full Name column
2. Choose Split Column > By Delimiter
3. Choose Space to separate the column into first and last names, and click OK.

Don’t try to fix every invalid date manually. Keep the dataset usable and continue cleaning other fields first. You can handle or filter invalid dates later during analysis or reporting.

If ZIP codes like 02138 or Product IDs like 000245 lose leading zeros, Power Query has likely inferred a numeric type during the automatic Changed Type step.

To fix this:

1. In the Applied Steps Pane, select the first Changed Type step.
2. Click the data type icon in the relevant column headers and select Text.
3. If prompted, click Insert to add the intermediate step.
4. Finally, click Replace current to update the existing step instead of creating a new one.

The leading zeros are now preserved correctly when the data is loaded into Excel.

To keep the dataset focused on useful fields like customer, order, and product data:

1. Hold Ctrl while selecting the Region and Internal Notes columns.
2. Right-click one of the selected columns and choose Remove Columns.
   
   

Once you’re happy with how your data looks, click Close & Load in the Home tab to load the cleaned Sales_Report_January.csv into Excel as a structured table.

At this point, you’ve turned a messy raw CSV into something usable in seconds.

Instead of repeating cleanup steps, Power Query can apply the same transformation logic across multiple CSV files in a folder.

First, follow these steps to connect Excel to the folder containing your reports:

1. In a new Excel workbook, click Data > Get Data > From File > From Folder.
2. Select the dedicated folder containing both CSV files.
3. Click Open.

This tells Excel to treat every CSV in the folder as part of a single combined table.

Next, define how the combined data should be structured:

1. Click Combine > Combine & Transform Data.
2. Choose the file you want to act as the sample file—usually the first or most complete report—and make sure the correct delimiter is selected (usually a comma) so that the columns are correctly separated.
3. Click OK.
4. In the Power Query Editor, review the combined data and perform additional cleanup if needed.

Click Close & Load in the Home tab to send the combined, cleaned report to a new Excel worksheet.

From here, whenever you drop new files into the folder, just click Data > Refresh All to update the query.