[go: up one dir, main page]
Scribd
Upload
142 views3 pages

Step-by-Step Data Cleaning in Power Query

This document outlines a step-by-step guide for data cleaning in Power Query, starting from loading data into Excel and removing unnecessary columns to handling missing values and correcting data formatting. It includes instructions for splitting columns, removing duplicates, changing data types, aggregating data, renaming columns, reviewing applied steps, and loading the cleaned data back into Excel. Additionally, it provides pro tips for efficient use of Power Query, emphasizing its non-destructive nature and the ability to refresh queries.

Uploaded by

SIR GUNZ
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
142 views3 pages

Step-by-Step Data Cleaning in Power Query

This document outlines a step-by-step guide for data cleaning in Power Query, starting from loading data into Excel and removing unnecessary columns to handling missing values and correcting data formatting. It includes instructions for splitting columns, removing duplicates, changing data types, aggregating data, renaming columns, reviewing applied steps, and loading the cleaned data back into Excel. Additionally, it provides pro tips for efficient use of Power Query, emphasizing its non-destructive nature and the ability to refresh queries.

Uploaded by

SIR GUNZ
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Step-by-Step Data Cleaning in Power Query

Step 1: Load Your Data into Power Query

1. Open Excel.
2. Go to the Data tab.
3. Click Get Data → From Workbook / From Table/Range.
4. Select your data range and click OK.
Ensure your data has headers.

Step 2: Remove Unnecessary Columns

1. Right-click on any irrelevant column header.


2. Click Remove to delete it.
3. You can also select multiple columns using Ctrl + Click, then right-click → Remove
Columns.

Step 3: Handle Missing Values

Option 1: Replace Nulls

1. Select the column with missing data (shows as null).


2. Right-click → Replace Values.
3. Find: null, Replace With: "N/A", 0, or a placeholder.

Option 2: Fill Down/Up

 Use when previous or next rows contain valid values:


o Go to the Transform tab → Fill → Down or Up.

Step 4: Correct Inconsistent Data Formatting

1. For inconsistent text cases, use:


o Format menu → Lowercase, Uppercase, or Capitalize Each Word.
2. For trailing/leading spaces:
o Select column → Transform tab → Format → Trim.
3. To remove invisible characters:
o Use Clean in Excel after load (Power Query doesn’t have CLEAN() built-in).

Step 5: Split Column (e.g., Full Name to First/Last)

1. Select the column you want to split (e.g., "John Doe").


2. Go to the Home or Transform tab.
3. Click Split Column → By Delimiter.
4. Choose:
o Space (for names),
o Comma or Custom Delimiter as needed.
5. Choose to split into columns (default), then click OK.

Step 6: Remove Duplicates

1. Select one or more columns (e.g., Email or ID).


2. Right-click → Remove Duplicates.
3. This will remove rows with duplicate values in the selected column(s).

Step 7: Change Data Types

1. Click the small icon next to the column header (shows current data type like “ABC”).
2. Choose the correct type:
o Text
o Whole Number
o Decimal Number
o Date/Time
3. Each change will be recorded as a step in Applied Steps.

Step 8: Aggregate Data (Summarize/Group By)

1. Go to the Home tab → Click Group By.


2. In the dialog:
o Group by: Choose a column (e.g., Region or Product).
o Operation: Choose Sum, Count, Average, etc.
o New Column Name: Give a name to the summary column.
3. Click OK. You’ll now see grouped/aggregated results.
Step 9: Rename Columns for Clarity

1. Double-click the column header or right-click → Rename.


2. Rename columns like “Column1” to something meaningful (e.g., “Customer Name”).

Step 10: Review Applied Steps

1. Look at the Applied Steps pane on the right.


2. Click each step to preview its effect.
3. Right-click a step to delete, reorder, or edit.
4. Click the gear icon (⚙️) on configurable steps to modify.

Step 11: Load the Cleaned Data

1. Once finished, click Home → Close & Load.


2. Your cleaned dataset will appear as a new table in Excel.

💡 Pro Tips for Power Query Cleaning


 Power Query is non-destructive: original data remains untouched.
 You can refresh the query anytime when the source file updates.
 Use Applied Steps as documentation of your cleaning workflow.
 Save cleaning steps as a template for recurring reports.

You might also like