Power Query for Beginners: Clean Excel Data

Power Query is Excel's repeatable way to import and clean data. You choose transformations from menus, and Excel records the steps so you can refresh the same cleanup when a new CSV or workbook export arrives. This guide stays with the beginner workflow: clean imported data first, then load a dependable table for formulas and reports.

1. What Power Query does

Power Query creates a separate query between your source file and the worksheet where you use the results. The source remains unchanged, while the query can promote headers, change types, trim text, remove rows, and reshape columns in a recorded sequence.

It is useful when the same kind of export arrives repeatedly. You build the cleanup once, then use Refresh after replacing or updating the source. For a one-time fix, a formula or Text to Columns may be quicker.

Here is a small imported table with the problems we will clean:

The IDs and names have extra spaces, dates use different patterns, the amount may be text, one row is blank, and the last row repeats the first record.

2. Import a CSV or Excel table

Open a blank workbook or the workbook that will hold the result. Choose **Data

Get Data** and then select From Text/CSV or From Workbook. Select the file and inspect the preview before changing anything.

For a CSV, check the delimiter and file origin. A comma inside a quoted address should stay in one field, and UTF-8 is a common choice for accented names. For an Excel source, select the worksheet or existing table that contains the data.

Choose Transform Data to open Power Query. Load is fine for a clean, one-time table, but it skips the transformation steps you need to reuse. If columns are already shifted in a CSV preview, fix the delimiter there before continuing; the clean imported CSV workflow has a troubleshooting checklist for encoding and column alignment.

3. Promote headers and check column types

In Power Query, look at the first few rows before editing. If the first row contains field names, choose Home > Use First Row as Headers. This makes later steps refer to names such as customer_id and order_date instead of generic columns like Column1.

Select each column and confirm its type from the icon beside the column name:

Power Query may guess a type and add an Changed Type step automatically. Review that step rather than assuming the guess is correct. If an identifier loses leading zeros or a date errors, select the column and choose the intended type explicitly. For more context on text numbers, see Excel numbers stored as text.

4. Trim and clean text columns

Select text columns such as customer_id, name, and status, then choose Transform > Format > Trim. Trim removes spaces at the start and end of a value. Use Clean from the same menu when imported text may contain line breaks or other non-printing characters.

After trimming, standardize labels such as paid and Paid with Transform > Format > UPPER, lower, or Capitalize Each Word, depending on the rule your report needs. Use Replace Values when you need a specific mapping, for example changing In progress to Open. The leading and trailing spaces guide explains the equivalent TRIM, CLEAN, and SUBSTITUTE formulas when you are not using Power Query.

Do not trim a free-form notes column if spaces or line breaks are meaningful. Apply a transformation only to columns where the source rule is clear.

5. Remove blanks and duplicate rows

Filter the key columns to find blank records. Select the filter arrow on customer_id and clear (null) and blank values when those rows are not valid records. If a blank row needs investigation, keep it in a separate query or flag it instead of silently deleting it.

To remove repeated records, first clean the columns that define sameness. Then select the relevant columns and choose Home > Remove Rows > Remove Duplicates. Selecting only customer_id is appropriate only when that ID is truly unique; select multiple columns when a record is identified by a combination such as ID and order date. The focused Remove Duplicates in Excel guide covers the same decision before deletion.

Power Query adds each choice as a step, so you can click a previous step to review the result and change the order if needed.

6. Split or standardize messy columns

For a column such as Full Name that consistently uses a delimiter, choose Transform > Split Column > By Delimiter. Select comma, space, or a custom delimiter, and preview the result before applying it. Rename the new columns to something meaningful, such as first_name and last_name.

When values use several patterns, standardize them with Replace Values or a conditional column. For example, map paid, Paid , and PAID to Paid, but do not merge different business states just because they look similar. Find and Replace in Excel is a faster option for a small, one-time list of known replacements.

You can also use Fill Down for repeated category labels, but only when a blank means "same as the value above." If the blank means unknown, leave it visible for review.

7. Load the cleaned table and refresh it later

When the preview looks right, choose Home > Close & Load (or Close & Load To to select a worksheet or the Data Model). Load the result to a new sheet so the raw source and cleaned output remain easy to compare.

When the next export has the same columns and location, replace the source file or update the source path, then choose Data > Refresh All. Power Query runs the recorded steps in order. If a new file has a renamed column or a different delimiter, the refresh may stop at the affected step; read the error, correct the source or step, and refresh again.

Keep the raw import unchanged and document any business rules, such as which columns define a duplicate. A refresh repeats instructions; it cannot decide whether an ambiguous blank or conflicting record is correct.

8. When formulas or Text to Columns are faster

Power Query is a good fit for repeatable imports, but it is not required for every cleanup:

For a broader decision guide, see Excel Data Cleaning. Use the simplest tool that keeps the source and the cleanup rule understandable.

9. Final checklist

Before using the loaded table in formulas or a report, verify:

• The source delimiter and encoding were correct.
• The first row is a header, not a data record.
• IDs, dates, amounts, and labels have deliberate column types.
• Text columns were trimmed and cleaned only where appropriate.
• Blank rows and required blank fields were reviewed.
• Duplicate criteria match the meaning of a record.
• Split or replaced values follow one documented standard.
• The loaded result matches a few rows in the raw source.
• Refresh All works with a test export.

If totals still ignore a numeric-looking column, revisit Excel numbers stored as text. If you need to remove duplicates in a single worksheet rather than maintain a refreshable query, use How to Remove Duplicates in Excel. Power Query is most valuable when the cleanup steps are clear enough to repeat safely.