If you regularly download raw CSV exports from your company’s CRM or financial software, you likely spend hours manually deleting blank rows, splitting columns, and fixing date formats. Repeating these mundane tasks every single week is an incredible waste of time and highly prone to human error.
Instead of manually hacking your spreadsheets or trying to write complex VBA macros, you need to learn how to use Power Query in Excel. Power Query is a visual data preparation tool built directly into modern versions of Microsoft Excel that allows you to automate data cleaning without writing a single line of code.
What is Power Query in Excel?
Power Query acts as a bridge between your messy raw data source and your final, pristine Excel report. When you load data into Power Query, you can apply a series of formatting steps (such as removing columns or filtering out null values). Power Query records these steps exactly like a macro.
The magic happens the following week: when you download a new raw data file, you simply click Refresh, and Power Query instantly reapplies every single cleaning step to the new data in seconds.
How to Build Your First Automated Data Workflow
To demonstrate the power of this tool, we will walk through a common scenario: cleaning up a messy sales export.
Step 1: Import Your Messy Data
- Open a blank Excel workbook.
- Navigate to the Data tab on the ribbon.
- Click on Get Data > From File > From Text/CSV.
- Locate your messy raw data file and click Import.
- A preview window will appear. Do not click Load yet! Instead, click Transform Data. This opens the Power Query Editor.
Step 2: Remove Unnecessary Columns
Raw data exports often include dozens of internal system columns that you do not need for your final report.
- In the Power Query Editor, hold down the Ctrl key and click the headers of the columns you actually want to keep.
- Right-click on any of the selected headers.
- Select Remove Other Columns.
Notice the “Applied Steps” panel on the right side of your screen. Power Query has recorded your action. From now on, it will always drop those extra columns automatically.
Step 3: Split Combined Columns
Often, a CRM will export names in a single “LastName, FirstName” format, but your manager wants them separated.
- Right-click the header of the column containing the names.
- Select Split Column > By Delimiter.
- Choose Comma from the dropdown menu and click OK.
- Power Query instantly splits the data into two new columns. You can double-click the new headers to rename them to “Last Name” and “First Name”.
Step 4: Clean Up Dates and Numbers
If your dates look like text or your currency has weird symbols, you can fix the data typing globally.
- Click the small icon on the left side of a column header (it usually looks like “ABC” or “123”).
- Select the correct data type from the list, such as Date or Currency. Power Query will forcefully convert the entire column to the correct format, preventing future pivot table errors.
Step 5: Load the Clean Data Back into Excel
Once your data looks perfect in the editor, it is time to bring it back to your spreadsheet.
- Click the Close & Load button in the top-left corner of the ribbon.
- Excel will create a new worksheet containing a beautiful, formatted green table with your perfectly clean data.
The Magic Step: Refreshing Your Data Next Week
The true power of Power Query is reusability. Next week, when you receive the new messy CSV file, you do not have to repeat any of the steps above.
- Save the new messy CSV file in exactly the same folder, with exactly the same filename, overwriting the old file.
- Open your Excel workbook containing your clean table.
- Right-click anywhere inside the green table and select Refresh.
Within milliseconds, Excel will pull in the new raw data, run it through the Power Query Editor in the background, apply your column splits and type conversions, and update your table. You have just automated your most annoying weekly task.
