Excel Power Query Tutorial for Beginners Step by Step

If you have ever spent hours manually cleaning data in Excel removing duplicates, fixing formats, combining files, unpivoting columns — Power Query was built specifically to eliminate that work.

Power Query is Excel’s built-in data transformation engine. It lets you connect to data sources, clean and reshape data, and load it into Excel through a point-and-click interface that records every step automatically. When your source data updates, you refresh with one click and all your transformations reapply instantly.

In this guide, we will walk through Power Query from scratch — what it is, how to access it, how to transform data step by step, and how to build repeatable data workflows that save hours of manual work.

What Is Power Query?

Power Query is a data connection and transformation tool built into Excel (and Power BI). It allows you to:

• Connect to virtually any data source — Excel files, CSV, databases, web pages, APIs, SharePoint, and more
• Transform data — clean, reshape, filter, merge, unpivot, and format
• Load the cleaned data into Excel as a table or into the data model
• Refresh — when source data changes, reapply all transformations with one click

Every transformation you apply is recorded as a step in the Query Editor. These steps are written in a language called M (Power Query Formula Language) behind the scenes but you rarely need to write M directly. The point-and-click interface generates it automatically.

Why Power Query Changes Everything

Before Power Query, cleaning data meant manual steps that had to be repeated every time the data updated. With Power Query:

• All steps are saved and reapplied automatically on refresh
• No VBA or macros required
• Works with massive datasets that would crash traditional Excel formulas
• Handles data from sources Excel could not previously connect to

Where to Find Power Query in Excel

Power Query lives in the Data tab of the Excel ribbon.

Data tab → Get & Transform Data section

You will see buttons for:

• Get Data — Connect to a data source
• From Text/CSV — Import a CSV or text file
• From Web — Pull data from a URL
• From Table/Range — Use data already in your Excel sheet
• Recent Sources — Quickly reconnect to sources you have used before
• Existing Connections — Manage saved connections

Once you load data into the Query Editor, the Power Query Editor window opens — a separate interface where all transformations happen.

Step 1: Connecting to a Data Source

Option A: From an Excel Table or Range

The simplest starting point — use data already in your workbook.

1. Click anywhere inside your data range
2. Press Ctrl + T to convert to a Table (or go to Insert → Table)
3. Go to Data tab → From Table/Range
4. Power Query Editor opens with your data loaded

Option B: From a CSV File

1. Data tab → Get Data → From File → From Text/CSV
2. Browse to your CSV file and click Import
3. A preview appears showing detected delimiter and data types
4. Click Transform Data to open the Query Editor (or Load to load directly without transformations)

Option C: From a Folder (Combining Multiple Files)

One of Power Query’s most powerful features — combining all files in a folder automatically.

1. Data tab → Get Data → From File → From Folder
2. Browse to the folder containing your files
3. Click OK → a preview shows all files in the folder
4. Click Combine → Combine and Transform Data
5. Power Query loads all files and stacks them into one dataset

When new files are added to the folder, a single refresh includes them automatically.

Option D: From Web

1. Data tab → Get Data → From Other Sources → From Web
2. Enter the URL containing the data
3. Power Query fetches the page and shows available tables
4. Select the table you want and click Transform Data

Step 2: Understanding the Power Query Editor

When you click Transform Data, the Power Query Editor opens. Take a moment to understand the interface before transforming anything.

The Four Main Areas

Query Pane (left) — Lists all queries in your workbook. You can have multiple queries and reference one from another.

Data Preview (center) — Shows a live preview of your data after each transformation step.

Applied Steps (right) — The most important panel. Every transformation you apply appears here as a named step. You can click any step to see the data at that point, delete steps to undo transformations, or drag steps to reorder them.

Ribbon (top) — Contains all transformation options organized into tabs: Home, Transform, Add Column, View.

The Applied Steps Panel

Understanding Applied Steps is the key to understanding Power Query.

When you first load data, Power Query automatically creates steps like:

• Source — connects to the data source
• Navigation — selects the specific table or sheet
• Promoted Headers — uses first row as column headers
• Changed Type — detects and applies data types

Every transformation you apply adds another step here. To undo something, simply delete the step. To review what happened at any point, click that step.

Step 3: Basic Transformations

Renaming Columns

1. Double-click a column header in the data preview
2. Type the new name and press Enter

Or: Right-click the column header → Rename

Applied Step added: Renamed Columns

Changing Data Types

Power Query automatically detects data types but sometimes gets it wrong. Dates might be detected as text, numbers as text.

1. Click the data type icon to the left of the column header (ABC for text, 123 for number, calendar for date)
2. Select the correct data type from the dropdown

Or: Right-click the column header → Change Type → select type

Important: Always set data types correctly before other transformations. Calculations and filters depend on the correct type.

Removing Columns

Select the columns you do not need:

• Click a column header
• Hold Ctrl and click additional column headers
• Right-click → Remove Columns

Or keep only the columns you want: Right-click → Remove Other Columns

Reordering Columns

Click a column header and drag it to the desired position.

Or: Select the column → Transform tab → Move → To Beginning / To End / Before / After

Filtering Rows

Each column has a dropdown arrow (like Excel AutoFilter):

1. Click the dropdown arrow on the column header
2. Uncheck values to exclude or use filter conditions
3. Click OK

For numeric columns: Filter Rows → Number Filters → is greater than, between, etc. For text columns: Filter Rows → Text Filters → Contains, Begins With, etc.

Removing Blank Rows

Home tab → Remove Rows → Remove Blank Rows

Removing Duplicates

1. Select the column(s) that define uniqueness
2. Home tab → Remove Rows → Remove Duplicates

Step 4: Text Transformations

Trimming Whitespace

Extra spaces before or after text cause silent data quality problems — lookups fail, filters miss matches.

Select the text column → Transform tab → Format → Trim

Changing Case

Select the column → Transform tab → Format → UPPERCASE / lowercase / Capitalize Each Word

Splitting a Column

Split one column into multiple columns — for example splitting “FirstName LastName” into two columns.

1. Select the column to split
2. Transform tab → Split Column → By Delimiter
3. Choose the delimiter (space, comma, dash, or custom)
4. Choose where to split: At each occurrence, At the leftmost, At the rightmost
5. Click OK — new columns appear

Merging Columns

Combine two columns into one:

1. Hold Ctrl and select multiple columns in order
2. Transform tab → Merge Columns
3. Choose a separator (space, comma, tab, or custom)
4. Name the new merged column
5. Click OK

Extracting Text

Extract specific parts of a text column:

Transform tab → Extract:

• Length — Count characters
• First Characters — Extract from the left
• Last Characters — Extract from the right
• Range — Extract from a specific position
• Text Before Delimiter — Everything before a character
• Text After Delimiter — Everything after a character
• Text Between Delimiters — Everything between two characters

Step 5: Number and Date Transformations

Rounding Numbers

Select the numeric column → Transform tab → Rounding → Round, Round Up, or Round Down

Basic Calculations on a Column

Transform tab → Standard → Multiply, Divide, Add, Subtract

This applies the operation to every value in the column using a constant you specify.

Date Transformations

Select a date column → Transform tab → Date:

• Year, Month, Day — Extract date components
• Age — Days since the date
• Start of Week/Month/Quarter/Year — Snap to period boundaries
• Day Name, Month Name — Extract readable names
• Week of Year, Quarter — Extract period numbers

These extractions automatically create new columns with the extracted values.

Step 6: Adding Custom Columns

Sometimes the built-in transformations are not enough, you need to create a new column based on your own formula.

Add Column Tab → Custom Column

1. Go to Add Column tab → Custom Column
2. Name your new column
3. Write a formula using the M language syntax

Example formulas:

// Combine first and last name
= [FirstName] & " " & [LastName]

// Calculate annual salary from monthly
= [MonthlySalary] * 12

// Classify salary as High, Medium, or Low
= if [Salary] >= 80000 then "High"
  else if [Salary] >= 50000 then "Medium"
  else "Low"

// Calculate days since order
= Duration.Days(DateTime.LocalNow() - [OrderDate])

Column references use square brackets. The M language is case-sensitive.

Add Column Tab → Conditional Column

For simple if-then-else logic, use the Conditional Column UI — no formula writing required.

1. Add Column tab → Conditional Column
2. Name the column
3. Set up your if-then-else rules using the dropdown menus
4. Click OK

This generates the M formula automatically — great for beginners who want conditional columns without learning M syntax.

Step 7: Grouping and Aggregating Data

Power Query can group rows and aggregate values — similar to a pivot table but applied as a transformation step.

1. Select the column(s) to group by
2. Transform tab (or Home tab) → Group By
3. In the Group By dialog:
   • Select grouping columns
   • Define aggregations: Sum, Average, Count, Min, Max, All Rows
4. Click OK

Example — Total sales per department:

Group By: Department New Column Name: Total Sales, Operation: Sum, Column: Revenue

Result:

Department	Total Sales
Engineering	348000
Marketing	201000
Sales	269000

Step 8: Unpivoting Columns

Unpivoting is one of Power Query’s most powerful transformations — converting wide format to long format automatically.

Scenario: Monthly sales columns (Jan, Feb, Mar… Dec) need to become rows.

1. Select the identifier columns (the ones you want to keep as rows like Name, Department)
2. Right-click → Unpivot Other Columns

All the monthly columns collapse into two columns: Attribute (the month name) and Value (the sales figure).

Or: Select the columns you want to unpivot → Transform tab → Unpivot Columns

Rename the Attribute column to “Month” and Value to “Sales” for clean output.

Step 9: Merging Queries (Like SQL JOIN)

Merging combines two queries based on matching column values exactly like a SQL JOIN.

1. Have two queries loaded in the Query Editor
2. Home tab → Merge Queries
3. Select the first query table (top)
4. Select the matching column in the top table
5. Select the second query from the dropdown (bottom)
6. Select the matching column in the bottom table
7. Choose the Join Kind:
   • Left Outer — All rows from first, matching from second (most common)
   • Inner — Only rows that match in both
   • Full Outer — All rows from both
   • Left Anti — Rows in first that have NO match in second
8. Click OK

A new column appears containing the merged table. Click the expand icon (two arrows) on that column header to select which columns to bring in from the second table.

Example — Add customer names to an orders table:

Orders query: OrderID, CustomerID, Amount Customers query: CustomerID, CustomerName, Region

Merge on CustomerID → brings CustomerName and Region into the Orders table.

Step 10: Appending Queries (Like SQL UNION)

Appending stacks two or more queries on top of each other like SQL UNION ALL. Use this when multiple datasets have the same structure and you want to combine them into one.

1. Home tab → Append Queries
2. Choose Two tables (for two queries) or Three or more tables (for multiple)
3. Select the queries to append
4. Click OK

The queries are stacked into one. Column matching is by name — columns with the same name are aligned, columns that exist in only one query get NULLs for the other.

Common use case: Combining monthly sales files (Jan_Sales, Feb_Sales, Mar_Sales) into one annual dataset.

Step 11: Loading Data Back to Excel

When you are done transforming, load the data back to Excel.

Home tab → Close & Load

Close & Load — Loads to a new sheet as an Excel Table.

Close & Load To — Opens a dialog with options:

• Table — Load as a Table in the current or new sheet
• PivotTable Report — Load directly into a pivot table
• PivotChart — Load into a pivot chart
• Only Create Connection — Load into the data model without creating a visible table (useful for queries that feed other queries)

Refreshing Data

When your source data changes:

• Right-click any query in the Queries & Connections pane → Refresh
• Or: Data tab → Refresh All to refresh every query at once

All your transformation steps reapply automatically to the new data.

Scheduling Automatic Refresh

For workbooks stored in SharePoint or OneDrive:

Data tab → Queries & Connections → Right-click a query → Properties → Usage tab → Check Refresh every X minutes or Refresh data when opening the file

Real-World Use Cases

Monthly Sales Report Automation

A sales manager receives 12 monthly CSV files. Previously they manually copied data into one sheet each month. With Power Query: connect to the folder, append all files, clean column names, filter to relevant date range, group by region, load to Excel. Each month they drop a new file in the folder and click Refresh — done in 10 seconds instead of 2 hours.

Data Cleaning Pipeline

A data analyst receives a weekly export from a CRM system with inconsistent formatting — mixed case names, leading spaces, missing values, merged date-time columns. Power Query applies trim, proper case, fill down, and split column transformations every week automatically on refresh.

Combining Data From Multiple Sources

A finance team needs to combine budget data from Excel with actuals from a SQL database and headcount from SharePoint. Power Query connects to all three, cleans and transforms each, merges them on cost center code, and loads the combined result for reporting.

Common Mistakes to Avoid

• Clicking Load instead of Transform Data — If you click Load on the import preview, Power Query loads the raw data without opening the editor. Always click Transform Data first to review and clean before loading
• Not setting data types — Leaving columns as the wrong type (number stored as text) breaks filters, calculations, and relationships downstream. Always set types explicitly after the Promoted Headers step
• Modifying the source data after connecting — If you move, rename, or restructure the source file, the Power Query connection breaks. Keep source files in stable locations
• Over-engineering with M code early on — Most transformations are available through the point-and-click interface. Learn the UI thoroughly before writing custom M — you rarely need it for most use cases
• Not using Close & Load To for large datasets — Loading very large transformed datasets as visible Excel tables can slow down the workbook. Use Only Create Connection for queries that feed other queries or pivot tables
• Forgetting to refresh after source updates — Power Query does not auto-refresh by default when the workbook is open. Always click Refresh All after updating source data unless you have enabled automatic refresh on file open

Power Query is one of the most transformative features in Excel — it genuinely changes how you work with data by automating the cleaning and preparation work that used to consume hours of manual effort every week.

Here is a quick recap of everything we covered:

• Power Query lives in the Data tab under Get & Transform Data
• Every transformation is saved as a step and reapplied automatically on refresh
• Basic transformations: rename, retype, filter, remove duplicates, trim whitespace
• Text transformations: split, merge, extract, change case
• Date and number transformations: extract components, round, calculate
• Custom columns: add new columns using M formulas or the Conditional Column UI
• Group By: aggregate data like a pivot table within the query
• Unpivot: convert wide format to long format for visualization
• Merge: combine queries like a SQL JOIN
• Append: stack queries like a SQL UNION
• Close & Load: output the cleaned data to Excel as a table

Start with one real cleaning task you do manually every week. Build it in Power Query, refresh it, and see the time savings immediately. From there, every additional query you build compounds the value.

FAQs