What is Power Query and How to Use It (Excel and Power BI)

TL;DR: Power Query is Microsoft’s tool for transforming and cleaning data without writing code. It’s in Excel and Power BI. If you spend hours with VLOOKUP, copying and pasting data, or manually cleaning spreadsheets, Power Query is a game-changer. This post covers what it is, where to find it, and the 10 transformations you’ll use most.

---

What is Power Query (and What It Does)

Power Query is a visual ETL (Extract, Transform, Load) tool built into Excel and Power BI. It lets you:

• Connect to almost any data source (Excel, CSV, databases, APIs, web…)
• Transform that data (clean, filter, merge, pivot…)
• Load the result into your spreadsheet or data model

All of this without writing code (though you can if you want) and in a repeatable way: set it up once, refresh forever.

The Problem It Solves

Sound familiar?

1. You receive an Excel file every month
2. It has poorly named columns, empty rows, inconsistent formats
3. You spend 2 hours cleaning it manually
4. Next month, you do it all over again

Power Query automates that process. Define the steps once, and every time new data arrives, just hit “Refresh”.

Where Power Query Lives

Application	Version	How to Access
Excel 365	Built-in	Data → Get Data
Excel 2019	Built-in	Data → Get Data
Excel 2016	Built-in	Data → New Query
Excel 2013	Add-in	Download and install separately
Excel 2010	Add-in	Download and install separately
Excel Mac	Limited	Data → Get Data (fewer connectors)
Power BI Desktop	Built-in	Home → Transform Data

Note on Excel Mac: It has Power Query, but with fewer connectors and features. If you’re serious about data work, use Windows or Power BI Desktop.

---

Where to Find Power Query in Excel

The most searched question. Here it is:

Excel 365 / 2019 / 2021 / 2024

1. Go to the Data tab
2. Find the Get & Transform Data group
3. Click Get Data

From there you can connect to:

• Files (Excel, CSV, XML, JSON, PDF…)
• Databases (SQL Server, MySQL, PostgreSQL, Oracle…)
• Online services (SharePoint, Dynamics, Salesforce…)
• Other sources (Web, OData, APIs…)

Excel 2016

1. Data tab
2. Get & Transform group
3. New Query

Excel 2013 and 2010

Power Query isn’t installed by default. You need to:

1. Download the add-in from Microsoft
2. Install it
3. A new “Power Query” tab will appear

My recommendation: If you’re still on Excel 2013 or earlier, upgrade. Modern Power Query is much better.

---

Your First Query in 5 Minutes

Let’s do a practical example. You have a sales CSV with messy data.

Step 1: Connect

1. Data → Get Data → From File → From Text/CSV
2. Select your file
3. A preview opens

Step 2: Transform

Power Query automatically detects columns and types. But your data has problems:

• Empty rows at the top
• A “Date” column stored as text
• Column names with weird spaces

Click Transform Data and the Power Query Editor opens.

Now apply transformations:

• Remove top rows (the empty ones)
• Use first row as headers
• Change type of the Date column to date
• Trim spaces from names

Step 3: Review Applied Steps

On the right you’ll see “Applied Steps”. Each transformation is a step:

Source
Navigation
Promoted Headers
Changed Type
Trimmed Text

You can click any step to see the data state at that point. You can delete steps, reorder them, or insert new ones.

Step 4: Load

When you’re satisfied:

1. Home → Close & Load
2. Choose where to load (table in sheet, connection only, data model)

Done. Next time a new file arrives, just change the source and refresh.

---

The 10 Most Useful Transformations

These cover 90% of what you’ll do with Power Query:

1. Remove Duplicates

Problem: You have repeated rows.

Solution:

1. Select the column(s) that define uniqueness
2. Right-click → Remove Duplicates

Or from the ribbon: Home → Remove Rows → Remove Duplicates

2. Filter Rows

Problem: You only want certain records (e.g., sales > 1000).

Solution:

1. Click the dropdown arrow on the column header
2. Uncheck values or use number/text/date filters

Like an Excel filter, but it stays saved.

3. Change Data Types

Problem: The “Price” column is stored as text.

Solution:

1. Click the type icon (ABC, 123, calendar) next to the column name
2. Select the correct type

Important: Always define types. Power Query is strict and type errors cause problems later.

4. Split Columns

Problem: You have “Full Name” and need “First Name” and “Last Name” separate.

Solution:

1. Select the column
2. Transform → Split Column → By Delimiter
3. Choose the delimiter (space, comma, etc.)

5. Merge Columns (Concatenate)

Problem: You have “First Name” and “Last Name” and want “Full Name”.

Solution:

1. Select both columns (Ctrl+click)
2. Right-click → Merge Columns
3. Choose separator

Or with a custom column:

[FirstName] & " " & [LastName]

6. Replace Values

Problem: The data says “Yes”, “YES”, “yes”, “Y” and you want to standardize.

Solution:

1. Select the column
2. Transform → Replace Values
3. Find “YES”, replace with “Yes”
4. Repeat for each variant

Tip: For multiple replacements, sometimes it’s easier to use a mapping table and do a Merge.

7. Unpivot Columns

Problem: You have data in “classic Excel format” with months as columns:

Product	January	February	March
A	100	150	200
B	80	90	100

And you need tabular format:

Product	Month	Sales
A	January	100
A	February	150
…	…	…

Solution:

1. Select the month columns
2. Transform → Unpivot Columns

This transformation is gold for data from typical Excel sheets.

8. Merge (The Powerful VLOOKUP)

Problem: You have a sales table and a products table. You want to add the product name to each sale.

Solution:

1. Home → Merge Queries → Merge Queries
2. Select the secondary table (Products)
3. Choose the join columns (ProductID in both)
4. Select the join type (Left, Inner, etc.)
5. Expand the columns you need

Join Types:

• Left Outer: All rows from first table, matches from second
• Right Outer: All rows from second table
• Full Outer: All rows from both
• Inner: Only rows that match in both
• Left Anti: Rows from first that are NOT in second

9. Append (Stack Tables Vertically)

Problem: You have January, February, and March sales in separate files. You want one table.

Solution:

1. Load all three queries
2. Home → Append Queries
3. Select the tables to combine

Requirement: Tables should have the same columns (or Power Query will try to align them).

Pro tip: Use a folder as your source. Power Query can load all files from a folder automatically.

10. Group By

Problem: You have individual transactions and want totals by customer.

Solution:

1. Transform → Group By
2. Group by: CustomerID
3. New column: TotalSales = Sum of Amount

Like a pivot table, but the result is a flat table you can keep transforming.

---

Power Query vs VLOOKUP: Why You Should Switch

If you use VLOOKUP for everything, you’ll love Power Query.

Example: Add Product Name to Sales

With VLOOKUP:

=VLOOKUP(A2, Products!$A$2:$B$100, 2, FALSE)

Problems:

• Breaks if you add columns to the products table
• Slow with lots of data
• You have to copy the formula to every row
• If the range changes, you have to update it

With Power Query (Merge):

1. Merge the two tables by ProductID
2. Expand the ProductName column
3. Done

Advantages:

• Doesn’t break when tables change
• Much faster with large data
• Updates automatically
• You can see exactly what it does (applied steps)

When to Stick with VLOOKUP

• One-off lookups you won’t repeat
• Small sheets that won’t grow
• Users who don’t have Power Query (old Excel)

For everything else, use Power Query.

---

Power Query vs Power Pivot vs DAX

These three tools work together. Confusion is normal.

Tool	What For	When Used
Power Query	Extract and transform data	Before loading to model
Power Pivot	Model data (relationships)	After loading
DAX	Calculate metrics	On the created model

The Complete Flow

Raw Data → [Power Query] → Data Model → [Power Pivot] → Relationships → [DAX] → Measures → Visualization

Practical example:

1. Power Query: Connect to a sales CSV, clean dates, remove duplicates, merge with products
2. Power Pivot: Load to model, create relationship between Sales and Calendar
3. DAX: Create measure Total Sales = SUM(Sales[Amount])
4. Visualization: Use the measure in a pivot table or chart

Do You Need All Three?

• Power Query only: If you just clean data for classic Excel
• Power Query + pivot tables: For basic analysis
• Power Query + Power Pivot + DAX: For serious data models (or Power BI)

---

Common Mistakes (and How to Avoid Them)

Mistake 1: Not Setting Data Types

Power Query tries to detect types automatically, but sometimes fails.

Problem: A postal code column like “08001” becomes the number 8001.

Solution: Always review types after loading. Change to text what should be text.

Mistake 2: Hardcoded File Paths

Source = Excel.Workbook(File.Contents("C:\Users\John\Desktop\data.xlsx"))

If you move the file or share it with someone, it breaks.

Solution: Use parameters.

1. Home → Manage Parameters → New Parameter
2. Define FilePath as a parameter
3. Use the parameter in the query

Mistake 3: Not Using Parameters for Changing Values

If you filter by year = 2024 and next year you need 2025, you’ll have to edit the query.

Solution: Create a CurrentYear parameter and use it in the filter.

Mistake 4: Ignoring Step Order

Order matters. If you filter rows before changing types, the filter might not work as expected.

General rule:

1. Promote headers
2. Change types
3. Filter and clean
4. Transform
5. Merge with other tables

Mistake 5: Not Documenting

Over time, you forget why you did certain steps.

Solution:

• Rename steps with descriptive names (right-click → Rename)
• Add comments in M code if needed

---

M Language: When to Touch It

Every step you take in Power Query generates code in M language (also called Power Query Formula Language).

Viewing M Code

1. View → Advanced Editor

You’ll see something like:

let
    Source = Excel.Workbook(File.Contents("data.xlsx")),
    Sheet1 = Source{[Name="Sheet1"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"Date", type date}})
in
    #"Changed Type"

Do You Need to Learn M?

To start: No. The visual interface covers 95% of cases.

Cases where M helps:

• Transformations not in the interface
• Complex conditional logic
• Custom functions
• Performance optimization

Example: Custom Column with M

If the interface doesn’t have what you need, you can write M directly:

1. Add Column → Custom Column
2. Write an M expression:

if [Amount] > 1000 then "Large" else "Small"

This is more flexible than menu options.

---

Power Query in Power BI vs Excel

The engine is the same, but there are differences:

Aspect	Excel	Power BI
Loads data to	Sheets or data model	Always to model
Interface	Integrated in Excel	Dedicated editor
Connectors	Many	Even more
Refresh	Manual or with macros	Scheduled in service
Sharing	The Excel file	Publish to Power BI Service

My recommendation:

• Excel: For ad-hoc analysis, sharing with Excel users, small data
• Power BI: For dashboards, large data, automatic refresh, web sharing

Learn Power Query in Excel and the knowledge transfers directly to Power BI.

---

Resources for Continued Learning

Official Documentation

• Power Query in Excel (Microsoft)
• M Functions Reference

Courses and Tutorials

• ExcelIsFun (YouTube) - Mike Girvin has excellent Power Query videos
• Leila Gharani (YouTube) - Clear and practical tutorials
• Ken Puls - Blog and book on Power Query

Books

• “M is for Data Monkey” (Ken Puls, Miguel Escobar) - The reference book
• “Collect, Combine and Transform Data Using Power Query” - More recent

Practice

• Download datasets from Kaggle or data.gov
• Try cleaning and transforming real data
• Replicate what you do manually in Excel

---

Conclusion: Where to Start

If you’re starting with Power Query:

1. Find Power Query in your version of Excel (Data → Get Data)
2. Load a CSV and explore the interface
3. Learn the 10 transformations from this post
4. Automate something you do manually every week/month
5. Measure time saved - you’ll be surprised

Power Query has a gentle learning curve. In an hour you can do useful things. In a week, transform how you work with data.

The investment is worth it: every process you automate saves you time forever.

---