Introduction to Power Query
Learn how Power Query helps you connect to data, clean inconsistent values, transform columns, filter rows, and prepare reliable datasets before building Power BI reports.
1. What Is Power Query?
Power Query is a data connection and transformation technology used in Power BI and other Microsoft products. It helps users retrieve data from different sources and prepare it for analysis.
What Can You Do with Power Query?
- Connect to Excel, CSV, databases, folders, and supported web sources.
- Remove unnecessary rows and columns.
- Rename columns and change data types.
- Replace values and handle missing data.
- Split columns and combine datasets.
- Merge or append queries.
- Save transformation steps for later refreshes.
Visual 1: Power Query combines data connection and preparation into a repeatable workflow.
2. Opening Power Query Editor
Power Query Editor provides the workspace where you can inspect source data and apply transformation steps before loading it into the Power BI data model.
Steps to Open Power Query Editor
- Open Power BI Desktop.
- Connect to a data source using Home → Get Data.
- Select the required file, table, or data object.
- Choose Transform Data to open Power Query Editor.
- Review the preview, query list, ribbon, and Query Settings pane.
Main Areas of Power Query Editor
- Queries pane: Lists queries available in the current Power Query session.
- Data preview: Displays a preview of the selected query.
- Ribbon: Provides transformation and data management commands.
- Formula bar: Displays the expression for a selected step when enabled.
- Query Settings: Shows query properties and Applied Steps.
Visual 2: The editor organizes queries, data preview, transformation commands, and applied steps.
3. Understanding Applied Steps
When you perform a transformation, Power Query usually records it as an applied step. These steps define how the original data is converted into the prepared result.
Example Transformation Sequence
- Source: Connect to the original dataset.
- Navigation: Select the required worksheet or table.
- Promoted Headers: Use the appropriate row as column headings.
- Changed Type: Assign appropriate data types.
- Removed Columns: Remove unnecessary fields.
- Filtered Rows: Keep the required records.
You can select a step to inspect the data at that point. Some steps can be edited or removed, but removing a step may affect later steps that depend on it.
4. Basic Data Cleaning Operations
Data cleaning improves consistency and helps prevent errors in calculations and visualizations. Choose transformations according to the actual data and business requirements.
| Operation | Purpose | Example |
|---|---|---|
| Remove Columns | Delete fields that are not required. | Remove an unused internal note. |
| Filter Rows | Keep records that meet selected conditions. | Keep sales from the current year. |
| Replace Values | Standardize known inconsistent values. | Replace an approved legacy category label. |
| Trim Text | Remove leading and trailing spaces. | Convert " West " to "West". |
| Change Data Type | Interpret values correctly. | Convert a text date into a date value. |
| Remove Duplicates | Remove repeated rows or values based on selected columns. | Keep one record per unique customer ID when appropriate. |
5. Example: Transforming Sales Data
Consider a sales dataset containing inconsistent region names, extra spaces, and amounts stored as text. The following example illustrates how transformation improves the data.
Before Transformation
| Order ID | Region | Amount |
|---|---|---|
| 101 | West | 5000 |
| 102 | West | 3000 |
| 103 | North | 2500 |
After Transformation
| Order ID | Region | Amount |
|---|---|---|
| 101 | West | 5000 |
| 102 | West | 3000 |
| 103 | North | 2500 |
Trimming whitespace makes the Region values consistent. The Amount column should also be assigned a numeric data type before performing numeric calculations.
Visual 3: A simple text transformation can prevent duplicate-looking categories in reports.
6. Understanding Data Types
Data types tell Power Query how to interpret values. Correct types help prevent calculation errors and support appropriate filtering, sorting, and modeling.
| Data Type | Example | Common Use |
|---|---|---|
| Text | Customer A | Names and categories |
| Whole Number | 25 | Counts and integer quantities |
| Decimal Number | 25.75 | Measurements and decimal values |
| Fixed Decimal Number | 125.50 | Suitable fixed-precision numeric scenarios |
| Date | 2026-05-12 | Calendar dates |
| Date/Time | 2026-05-12 10:30 | Date and time values |
| True/False | True | Logical conditions |
7. Apply Changes and Load Data
After preparing the data, use Close & Apply in Power BI Desktop to apply the query changes and load the results into the data model, subject to the current connection and model configuration.
Before You Apply Changes
- Check column names for clarity.
- Verify the data types.
- Review null values and errors.
- Confirm that required rows are present.
- Check whether any transformation removed needed data.
- Rename queries to meaningful names.
Visual 4: Prepared query results can be applied to the Power BI data model.
8. Practical Exercise
Exercise: Clean a Sales Dataset
Scenario: A sales team receives a spreadsheet with inconsistent region names and incorrectly typed amounts.
- Import the spreadsheet into Power BI Desktop.
- Select Transform Data.
- Rename the query to SalesData.
- Remove any unnecessary columns.
- Trim the Region column to remove extra spaces.
- Set Amount to a suitable numeric type.
- Review and correct any data conversion errors.
- Inspect the Applied Steps list.
- Select Close & Apply.
- Create a simple table visual to verify the loaded data.
Expected outcome: A clean and consistently typed sales dataset ready for modeling and reporting.
9. Knowledge Check
Question 1: What is the primary purpose of Power Query?
A. Design slide presentations
B. Connect to and transform data
C. Create operating system accounts
D. Format documents
Question 2: What does the Applied Steps list show?
A. The sequence of query transformations
B. Only report colors
C. Only published workspaces
D. A list of users
Question 3: Which operation removes leading and trailing spaces from text?
A. Merge
B. Pivot
C. Trim
D. Append
Question 4: What does Close & Apply generally do in Power BI Desktop?
A. Delete all queries
B. Apply query changes and load results according to the model configuration
C. Publish the report automatically
D. Remove data source credentials
View Answers
- B — Connect to and transform data.
- A — The sequence of query transformations.
- C — Trim.
- B — Apply query changes and load results according to the model configuration.
Summary
In this lesson, you learned what Power Query is, how to open Power Query Editor, and how to inspect queries, data previews, and Applied Steps.
You also practiced basic data cleaning, reviewed common data types, and learned how to apply transformations before using the prepared data in the Power BI model.