Power Query: Complete Data Transformation Guide

Advanced25 min readUpdated: July 2024

Available in Excel 2016+:

Power Query is built into Excel 2016 and later. If you're using an older version, you can download it as an add-in. This guide focuses on the modern integrated version.

1. What is Power Query?

Power Query Definition

Power Query is Excel's built-in ETL (Extract, Transform, Load) tool that allows you to import data from various sources, clean and transform it, then load it into Excel for analysis.

Extract

Get data from sources

Transform

Clean and shape data

Load

Bring into Excel

Why Use Power Query?

✓ Advantages

•No programming knowledge required
•Connects to 100+ data sources
•Automatically refreshes data
•Repeatable transformations
•Handles large datasets efficiently

Common Use Cases

•Cleaning messy data imports
•Combining data from multiple files
•Transforming data structure
•Creating automated reports
•Web data scraping

2. Getting Started with Power Query

Where to Find Power Query:

In Excel, go to Data tab → Get Data or look for the "Get & Transform Data" section.

Your First Power Query

Step-by-Step: Import and Clean a CSV File

Get Data:
Data tab → Get Data → From File → From Text/CSV
Select File:
Choose your CSV file and click "Import"
Preview Data:
Excel shows a preview - click "Transform Data" to open Power Query Editor
Transform:
Make your changes in the Query Editor
Load Data:
Click "Close & Load" to bring data into Excel

Key Concepts to Understand

Query Steps:

Each transformation creates a step that you can modify or remove

Data Preview:

Shows sample data while you work, not the full dataset

Non-Destructive:

Original data stays unchanged; transformations are applied on load

M Language:

Power Query generates M code automatically (you can view/edit it)

3. Connecting to Data Sources

Common Data Sources

Files

• Excel workbooks
• CSV/Text files
• JSON files
• XML files
• PDF tables

Databases

• SQL Server
• Access databases
• MySQL
• Oracle
• PostgreSQL

Cloud Services

• SharePoint lists
• OneDrive files
• Azure services
• Salesforce
• Google Analytics

Web Sources

• Web pages (scraping)
• Web APIs
• OData feeds
• RSS feeds

Other Excel

• Other worksheets
• Tables/ranges
• Named ranges
• Pivot tables

Folders

• Multiple files
• File properties
• Folder monitoring
• Batch processing

Connection Examples

Example 1: Web Data (Stock Prices)

Data → Get Data → From Other Sources → From Web
Enter URL (e.g., finance website with tables)
Select the table you want from the Navigator
Click "Transform Data" to clean it up

Example 2: Multiple Excel Files in a Folder

Data → Get Data → From File → From Folder
Select folder containing Excel files
Click "Combine & Transform Data"
Power Query automatically combines all files

Example 3: SQL Database

Data → Get Data → From Database → From SQL Server Database
Enter server name and database name
Choose authentication method
Select tables or write custom SQL query

4. Power Query Editor Interface

Query Editor Layout

Ribbon

Transformation tools and commands

Queries Pane

List of all queries in workbook

Data Preview

Sample of transformed data

Query Settings

Applied steps and properties

Important Ribbon Tabs

Home Tab (Most Used)

Manage Rows:

• Remove Rows
• Keep Rows
• Remove Duplicates
• Sort

Manage Columns:

• Remove Columns
• Choose Columns
• Move Columns
• Rename

Transform:

• Data Type
• Replace Values
• Fill Down/Up
• Pivot/Unpivot

Transform Tab

Text Column:

• Split Column
• Merge Columns
• Extract
• Format (Upper/Lower)

Date & Time:

• Date Parts
• Age
• Duration
• Parse

Number Column:

• Statistics
• Standard
• Scientific
• Trigonometry

Query Settings Pane

Applied Steps:

• Shows each transformation
• Click to see result at that step
• Right-click to edit or delete
• Drag to reorder steps

Properties:

• Query name
• Description
• Load settings
• Refresh settings

5. Basic Data Transformations

Column Operations

Remove and Select Columns

Remove Unwanted Columns:

1. Select column(s) to remove
2. Right-click → Remove Columns
3. Or: Home → Remove Columns

Keep Only Needed Columns:

1. Select column(s) to keep
2. Right-click → Remove Other Columns
3. Or: Home → Choose Columns

Rename and Reorder Columns

Rename Columns:

• Double-click column header
• Right-click → Rename
• F2 key
• Transform → Rename

Reorder Columns:

• Drag column headers
• Right-click → Move
• Home → Move Columns

Change Data Types

Proper data types are crucial for calculations and analysis:

Data Type	When to Use	Example
Text	Names, codes, descriptions	"John Smith", "PROD001"
Whole Number	Counts, IDs, quantities	1, 25, 1000
Decimal Number	Prices, percentages, measurements	19.99, 0.15, 5.5
Date	Dates only	2024-01-15
Date/Time	Timestamps	2024-01-15 14:30:00

How to change: Select column → Home → Data Type dropdown

Row Operations

Filter and Remove Rows

Filter Rows:

1. Click dropdown arrow in column header
2. Uncheck values to hide
3. Or use Text Filters/Number Filters
4. Click OK

Remove Rows:

• Remove Top Rows
• Remove Bottom Rows
• Remove Blank Rows
• Remove Errors
• Remove Duplicates

Sort Data

Simple Sort:

• Click column header dropdown
• Choose "Sort Ascending" or "Sort Descending"
• Or: Home → Sort → Sort Ascending/Descending

Multi-Column Sort:

1. Home → Sort Rows
2. Choose first sort column
3. Click "Add sort level" for additional columns
4. Set sort order for each

6. Advanced Transformations

Text Transformations

Split and Merge Columns

Split Column:

Transform → Split Column →

• By Delimiter (comma, space, etc.)
• By Number of Characters
• By Positions
• By Lowercase to Uppercase

Merge Columns:

1. Select multiple columns (Ctrl+click)
2. Transform → Merge Columns
3. Choose separator (space, comma, etc.)
4. Name the new column

Example: Split Full Name

"John Smith" → Split by Space → "John" and "Smith"

Text Cleaning

Format Text:

• UPPERCASE
• lowercase
• Capitalize Each Word
• Trim (remove spaces)

Extract Text:

• Length
• First Characters
• Last Characters
• Range of Characters

Replace Values:

• Home → Replace Values
• Find and replace text
• Support for partial matches
• Case-sensitive options

Pivot and Unpivot

Unpivot Columns (Wide to Long Format)

Convert wide data format to long format for analysis:

Before (Wide Format):

Product	Jan	Feb	Mar
A	100	120	110
B	80	90	85

After (Long Format):

Product	Month	Sales
A	Jan	100
A	Feb	120
A	Mar	110

How to: Select columns to unpivot → Transform → Unpivot Columns

Pivot Columns (Long to Wide Format)

Select the column that contains values you want as new column headers
Transform → Pivot Column
Choose the values column (data to fill the pivoted table)
Select aggregation function (Sum, Count, Average, etc.)
Click OK

Custom Columns

Add Custom Column

Create calculated columns using M language expressions:

Basic Examples:

Full Name:

[First Name] & " " & [Last Name]

Total Price:

[Quantity] * [Unit Price]

Conditional Logic:

if [Sales] > 1000 then "High" else if [Sales] > 500 then "Medium" else "Low"

Date Calculations:

Date.Year([Order Date])

7. Combining Multiple Data Sources

Append Queries (Union)

Combine Tables with Same Structure

Append stacks tables on top of each other (like UNION in SQL):

Home → Append Queries
Choose "Two tables" or "Three or more tables"
Select the queries to combine
Power Query automatically matches column names
Click OK

Use Case: Combining sales data from multiple months or regions

Merge Queries (Join)

Join Tables Based on Common Columns

Merge combines tables side by side based on matching values:

Join Types Available:

• Inner Join: Only matching rows
• Left Outer: All from left table
• Right Outer: All from right table

• Full Outer: All rows from both
• Left Anti: Left table only (no matches)
• Right Anti: Right table only (no matches)

How to Merge:

Home → Merge Queries
Select the second table
Click on matching columns in both tables
Choose join type
Click OK
Expand the merged column to see joined data

8. Working with Data Types

Why Data Types Matter

Proper data types ensure calculations work correctly, sorting behaves as expected, and downstream analysis tools interpret your data properly.

Common Data Type Issues & Solutions

Text Numbers

Problem:

Numbers imported as text can't be used in calculations

"123" instead of 123

Solution:

1. Select the column
2. Home → Data Type → Decimal Number
3. Or: Transform → Detect Data Type

Date Issues

Common Date Problems:

• Mixed date formats (MM/DD/YYYY vs DD/MM/YYYY)
• Text dates that aren't recognized
• Excel serial numbers
• Different locale settings

Solutions:

Parse text dates:

Transform → Date → Parse

Custom date format:

Data Type → Using Locale

Handling Errors

Error Types:

• DataFormat.Error
• Expression.Error
• DataSource.Error
• Token.Error

Remove Errors:

• Home → Remove Rows → Remove Errors
• Transform → Replace Errors
• Keep rows without errors

9. Refresh and Automation

Manual Refresh

Refresh Options

Refresh All:

Data → Refresh All

Updates all queries in workbook

Refresh Selected:

Right-click table → Refresh

Updates specific query only

Background Refresh:

Query Properties → Enable background refresh

Doesn't block Excel while refreshing

Automatic Refresh

Refresh Settings

Auto-refresh Options:

Right-click query in Queries pane
Select "Properties"
Check "Refresh every" and set minutes
Optional: "Refresh data when opening the file"
Click OK

Best Practices:

• Don't set refresh too frequently (causes performance issues)
• Use background refresh for large datasets
• Test refresh with small data first
• Document refresh dependencies

10. Best Practices & Tips

✓ Best Practices

✓Name your queries descriptively
✓Document complex transformations
✓Set proper data types early
✓Use staging queries for complex projects
✓Test with sample data first
✓Keep source data unchanged

✗ Common Mistakes

✗Not setting data types properly
✗Creating overly complex single queries
✗Ignoring refresh performance
✗Hard-coding file paths
✗Not handling data source changes
✗Mixing data transformation with analysis

Performance Tips

Optimize Query Performance:

• Filter data as early as possible
• Remove unnecessary columns early
• Use query folding when possible
• Avoid complex calculated columns in large tables
• Use staging queries for reusable logic

Memory Management:

• Close Power Query Editor when not in use
• Limit preview rows for large datasets
• Use background refresh for large queries
• Consider data model size limits
• Split very large files into smaller chunks

Troubleshooting Common Issues

Query Folding

Query folding pushes transformations back to the data source for better performance:

Folding-Friendly Operations:

• Select/remove columns
• Filter rows
• Sort
• Group by
• Join operations

Folding-Breaking Operations:

• Custom columns with M functions
• Pivot/unpivot
• Some text transformations
• Add index column

Error Handling

DataSource.Error:

Check file paths, network connections, and permissions

DataFormat.Error:

Verify data types and handle inconsistent formatting

Expression.Error:

Review custom column formulas and M syntax

Practice Exercise: Sales Data Integration

Challenge: Create an Automated Sales Dashboard

Build a complete data pipeline using Power Query:

Data Sources:
- Sales transactions (CSV files in a folder)
- Product catalog (Excel file)
- Customer information (database or web source)
Transformations:
- Combine all sales files from folder
- Clean and standardize data formats
- Join with product and customer data
- Create calculated columns (profit margin, customer segment)
- Handle missing data and errors
Output:
- Clean, integrated dataset ready for analysis
- Automatic refresh when new files are added
- Error handling for data quality issues

Success Criteria: Dashboard automatically updates when new sales files are added to the folder, handles data quality issues gracefully, and provides clean data for pivot tables and charts.

Excel Array Formulas & SPILL Functions

25 of 25

🎉 Tutorial Complete!

Browse All Tutorials