How to Use Power Query to Clean Excel Data

People have always used Excel for quick calculations and creating simple tables. But beyond common formulas and basic data manipulation techniques, you may never feel the need to learn more Excel functions—until projects start to get complicated.

 

How to Use Power Query to Clean Up Data

Many people have decided to follow a simple step-by-step process in Power Query Editor. This is exactly how they clean up those messy CSV exports and turn them into a consistent, well-structured spreadsheet.

First, import the data into Power Query Editor by opening a blank workbook, clicking Data on the ribbon, and selecting From Text/CSV . Then, select the CSV file and click Transform Data to open the file in Power Query Editor.

 

Start by fixing the Date column . Since the example is collecting data from two sources with timestamps 12 hours apart, the author needs to standardize the dates. This turns out to be incredibly simple. Just select the Date column , right-click to open the context menu, and select Change Type > Using Locale . In the pop-up menu, set the type to Date and select English (United States) to ensure consistent formatting. Power Query then automatically recognizes different formats, such as MM/DD/YYYY, YYYY/MM/DD, and variations that use notations like DD-MM-YY, and normalizes them all to a single date format.

 

Now that the date format is fixed, all that needs to be done is clean up the column. There are a number of ways to clean up an Excel spreadsheet, but since all errors are erroneous entries made by the data collection tool, simply select the Remove Errors filter to remove those entries. This will remove any null values ​​and any remaining problematic data that wasn't recorded correctly, leaving you with clean, consistent dates across all your files.

 

Next, we need to resolve the brand name confusion using the Replace Values ​​function . As before, select the target column, then right-click to open the context menu and select Replace Values . In the pop-up window, enter the inconsistent value in the Value to Find field and the standard value in the Replace With field .

Do this about two more times and finally got all the "gigabyte" and "GIGABTYE Inc." values ​​to a single "GIGABYTE" value across all files. Do the same for AMD and now the entire Brand column for the GPU uses the standard brand name.

 

Finally, we need to normalize the Price column . This is a bit tricky because many of the entries are just numbers, with no indication of whether they are in US dollars or Philippine pesos, making conversion difficult. Luckily, the Store column is right next to it, allowing us to easily convert all of our Shoppe PH prices to USD.

The way to fix this is to first use Replace Values ​​to remove all $, ₱, USD, and PHP from the Price column. Then, convert the values ​​to decimals using Change Type > Decimal . With a clean column containing only decimals, create a new column by clicking Column (ribbon) > Custom Column and name it "Price USD". Then, enter the following formula:

if [Store] = "Shopee PH" then Number.Round([Price] / 55, 2) else Number.Round([Price], 2)

Then press OK . This will convert all prices from Shopee PH to USD for easy comparison.

 

Formulas are written in the Power Query M formula language. It is a fairly simple scripting language with simple core concepts that are easy to grasp. You don't need to learn the entire language to use it effectively. Many people just use it as a reference whenever they need to build their own formulas.

Now that everything is in place, just close the Power Query Editor and select Keep to save all the changes in the created workbook.

Power Query will save hours of time

One reason many people avoid using Power Query is because they think it will be a complicated feature that will take too long to learn. But it turns out it's a lot easier than it sounds. Instead of running countless find and replace commands, you can use Power Query to quickly and automatically clean up data from your data collection tools.

What's even more amazing about Power Query is that every command executed is logged and can be repeated multiple times. This essentially gives you an automated cleanup script that can transform messy CSV files into clean, structured spreadsheets—perfect if you're creating custom datasets using web crawlers, as those tools often output messy data.

For anyone dealing with repetitive data cleanup, inconsistent formatting, or multiple data sources, Power Query turns those burdens into a simple, automated process. Instead of spending hours each week manually fixing errors, you can simply hit Refresh and start analyzing. It's an Excel feature that many people wish they had long ago. Once you experience the power of an automated, reproducible cleanup script, you won't want to go back.