Power Query in Excel
Learn how to import, clean, transform, combine, and refresh data using Excel Power Query.
1. What Is Power Query?
Power Query is a data connection and transformation tool available in supported versions of Excel. It helps users import data from different sources, apply repeatable cleaning steps, combine datasets, and load the prepared results into a worksheet or data model.
Power Query is useful when you need to:
- Import data from Excel workbooks, CSV files, and other supported sources.
- Remove unnecessary columns and rows.
- Correct data types and clean text.
- Combine data from multiple files or tables.
- Repeat the same transformations when source data changes.
- Prepare clean data for PivotTables and reports.
2. Power Query Workflow
Image 1 — Import, Transform, Load
Custom illustration of the typical Power Query workflow.
3. How to Open Power Query
- Open Microsoft Excel.
- Select the Data tab.
- Find the Get Data or data import options.
- Select a supported source, such as From Text/CSV or From Workbook.
- Select the file or source you want to import.
- Choose Transform Data to open the Power Query Editor.
4. Sample Data for Power Query
Create a CSV file named StudentSales.csv with the following sample data. We will use it to practice common transformations.
StudentID,StudentName,Course,City,FeesPaid
101, Samadhan ,Java,Pune,45000
102,Samiksha,SQL,Mumbai,60000
103,Pradnya,Python,Pune,32000
104,Omkar,C#,Mumbai,75000
105,Neha,Java,Pune,28000
106,Samiksha,SQL,Mumbai,60000
Notice that the first student's name contains extra spaces, and one record is repeated. These are intentional examples for practicing data cleaning.
5. Explore the Power Query Editor
Image 2 — Power Query Editor Layout
Simplified illustration. Actual editor layout varies by Excel version.
The Query list shows available queries, the data preview displays transformed data, and Applied Steps records the transformations performed in the current query.
6. Remove Unnecessary Columns
Sometimes a source contains columns that are not needed in the final report. Power Query can remove them without manually editing every row.
Steps
- Open the query in Power Query Editor.
- Select the column you do not need.
- Right-click and select Remove, or use the relevant ribbon option.
- Review the preview to confirm that only the intended column was removed.
Example: If the final report needs StudentID, StudentName, Course, and FeesPaid, you may remove City only if the report does not need it.
7. Clean Text Using Power Query
Imported text may contain unwanted leading or trailing spaces. Power Query provides text transformation options such as Trim and Clean.
| Before | Operation | Expected Result |
|---|---|---|
Samadhan |
Trim | Samadhan |
| Text containing unwanted control characters | Clean | Text with removable control characters removed |
java |
Capitalize Each Word, if appropriate | Java |
Steps to Trim Text
- Select the relevant text column, such as StudentName.
- Open the Transform tab or the available text transformation menu.
- Choose Format → Trim, where available.
- Check the preview and confirm that the spaces have been removed.
8. Before and After Data Cleaning
Image 3 — Cleaning Student Names
9. Change Data Types
Data types determine how values are interpreted. Power Query allows you to assign appropriate types such as Text, Whole Number, Decimal Number, Date, and Date/Time.
| Column | Suggested Type | Reason |
|---|---|---|
| StudentID | Text or Whole Number, depending on requirements | Text is safer when leading zeros or non-numeric identifiers matter. |
| StudentName | Text | Stores names. |
| Course | Text | Stores course labels. |
| FeesPaid | Whole Number or Decimal Number | Allows numeric calculations when values are valid. |
| AdmissionDate | Date | Allows date-based filtering and analysis. |
Steps
- Select the column header.
- Open the data type menu, often shown by an icon beside the column name.
- Select the correct type.
- Review the preview for errors or unexpected conversions.
10. Filter Rows in Power Query
Filtering allows you to keep only the records needed for a report. For example, you can filter the City column to keep only Pune records.
Steps to Filter Pune Students
- Select the filter dropdown on the City column.
- Clear the values you do not want to keep.
- Select Pune.
- Apply the filter.
- Review the remaining rows in the preview.
11. Append vs Merge Queries
Power Query supports two important ways to combine data: Append and Merge. They solve different problems.
Image 4 — Append and Merge Compared
Custom conceptual illustration. Check column names and join settings before combining real datasets.
Append Queries
Append adds rows from one table below another. It is useful when two tables have similar columns, such as January and February admissions.
Merge Queries
Merge matches records between tables using one or more key columns. For example, merge StudentDetails and FeeDetails using StudentID. The join kind determines which matching and non-matching records remain.
12. Load and Refresh Query Results
Load the Data
- Review the query preview and applied steps.
- Select Close & Load or the equivalent available command.
- Choose a worksheet or supported data model destination when offered.
- Check the output for missing columns, errors, or unexpected rows.
Refresh the Query
- Update the source file or source records as required.
- Ensure that the source remains accessible.
- Use Refresh or Refresh All in Excel.
- Review the refreshed output and check for errors.
13. Practical Exercises
Task 1: Import a CSV File
Import StudentSales.csv through Data → From Text/CSV and inspect the column types in Power Query Editor.
Task 2: Trim Student Names
Apply Trim to StudentName and verify that unnecessary leading and trailing spaces have been removed.
Task 3: Filter Pune Records
Filter City to retain Pune records and confirm that the output contains only the intended rows.
Task 4: Append Two Tables
Create January and February tables with matching columns and append them into a combined table.
Task 5: Merge Student and Fee Tables
Merge two tables using StudentID, expand the fee information, load the result, and test refreshing the query.
Important Points to Remember
- Power Query supports repeatable import and transformation workflows.
- Applied Steps records the transformations in a query.
- Trim and Clean solve different text-cleaning problems.
- Set data types carefully before analysis.
- Append stacks rows; Merge matches related records.
- Check join keys and duplicate records before combining tables.
- Refresh the query and validate the result after source changes.
Summary
Power Query helps import, clean, transform, combine, and load data from supported sources. Its Applied Steps make data preparation repeatable, reducing the need to manually perform the same tasks each time a source changes.
Use Trim to remove unwanted surrounding spaces, set suitable data types, filter records carefully, and choose Append or Merge according to the structure of your data. Always validate the final output after loading or refreshing.