How to Use Get and Transform in Excel for Data Cleaning

Use Excel's Get and Transform (Power Query) to import data from various sources, clean it, and reshape it without changing the original file. Open it via the Data tab, then select Get Data to start importing and transforming your data with an easy-to-use interface.

Syntax

Get and Transform in Excel uses a graphical interface rather than formulas, but its core steps include:

• Data > Get Data: Choose your data source (Excel file, CSV, web, etc.).
• Power Query Editor: Clean and transform data using commands like Remove Columns, Filter Rows, Split Columns, and Merge Queries.
• Close & Load: Load the transformed data back into Excel as a table or connection.

Each step you apply is recorded and can be edited or removed anytime.

excel

No code syntax; uses GUI steps in Excel.

Example

This example shows how to import a CSV file, remove unwanted columns, and load the cleaned data into Excel.

text

1. Go to <strong>Data</strong> tab &gt; <strong>Get Data</strong> &gt; <strong>From File</strong> &gt; <strong>From Text/CSV</strong>.
2. Select your CSV file and click <strong>Import</strong>.
3. In the preview window, click <strong>Transform Data</strong> to open Power Query Editor.
4. Right-click the columns you don't need and select <strong>Remove</strong>.
5. Click <strong>Close & Load</strong> to load the cleaned data into a new worksheet.

Output

A new Excel table with only the selected columns from the CSV file.

Common Pitfalls

• Not refreshing the query after the source data changes, so your table stays outdated.
• Removing columns or steps accidentally in Power Query, which can break your data load.
• Loading large data sets without filtering first, causing slow performance.
• Confusing Close & Load To... options; choose table or connection depending on your need.

text

Wrong way:
- Import data and close without transforming, then try to clean data manually.
Right way:
- Use Power Query Editor to clean data before loading it into Excel.

Quick Reference

Step	Description
Get Data	Import data from files, databases, or web.
Transform Data	Clean, filter, and reshape data in Power Query Editor.
Apply Steps	Each action is recorded and editable.
Close & Load	Load the transformed data into Excel as a table or connection.
Refresh	Update the data when source changes.

Key Takeaways

Use the Data tab's Get Data button to start importing data with Get and Transform.

Clean and reshape data easily in the Power Query Editor before loading it into Excel.

Always refresh your queries to keep data up to date with source changes.

Remove unnecessary columns and filter rows early to improve performance.

Use Close & Load options carefully to choose how transformed data appears in Excel.