Power Query Tutorial for Beginners: Clean and Combine Data in Excel

This Power Query tutorial for beginners shows how to clean and combine data in Excel without formulas or macros. Power Query records every step, so next month you just click Refresh. It works the same way in Power BI.

What Is Power Query?

Power Query is a data preparation tool built into Excel (2016 and later, and Microsoft 365) and Power BI. It imports data from files, folders, databases and the web, lets you clean and reshape it with menu clicks, and saves the steps as a repeatable query.

Where to Find Power Query in Excel

Go to Data → Get Data (or Data → From Text/CSV). After choosing a file, click Transform Data to open the Power Query Editor.

Step 1: Import and Clean a File

1. Use First Row as Headers if needed.
2. Remove Rows → Remove Blank Rows and remove top rows with titles.
3. Change data types (date, whole number, decimal) from the column header icon.
4. Transform → Format → Trim and Clean to remove extra spaces.
5. Split Column by delimiter (for example “City, State”).
6. Remove Duplicates on the key column.
7. Close & Load to send the result to a sheet.

Step 2: Merge Queries (Like VLOOKUP)

Home → Merge Queries, choose the two tables and the matching column (for example customer_id), pick the join kind (Left Outer is like VLOOKUP), then expand the columns you need. Use Left Anti join to list rows with no match: a fast way to find missing records.

Step 3: Append Many Monthly Files

Put all monthly CSVs in one folder, then Data → Get Data → From File → From Folder → Combine & Transform. Power Query stacks them into one table and adds the file name, which you can turn into a Month column.

Step 4: Group By and Unpivot

• Transform → Group By: total amount per region, like a PivotTable that refreshes with the data.
• Unpivot Columns: turn Jan, Feb, Mar columns into Month and Value rows, the shape most reports need.

Step 5: Refresh

Replace the source file or add a new file to the folder, then Data → Refresh All. Every step re-runs automatically.

A Look at M Code

Each click writes a line of M code, visible in View → Advanced Editor:

let
    Source = Csv.Document(File.Contents("C:\data\sales.csv"), [Delimiter=",", Encoding=65001]),
    Headers = Table.PromoteHeaders(Source),
    Typed = Table.TransformColumnTypes(Headers, {{"order_date", type date}, {"amount", type number}}),
    NoDupes = Table.Distinct(Typed, {"order_id"})
in
    NoDupes

Tip: AI assistants such as Claude can explain or write M steps for you; see Claude prompts for data analysis (prompt 20).

Power Query for Testers

Testers use Power Query to compare source and target extracts without SQL access: load both files, Merge with Left Anti to find missing rows, and Group By to compare totals. It is a quick, repeatable reconciliation that business users can also understand. For SQL-based checks see data reconciliation.

Claude AI for Data Analysis & Business Intelligence includes a 1-hour Power Query section alongside Claude AI prompts, pandas, EDA and Excel feature engineering.

Frequently Asked Questions

What is Power Query used for?

Importing, cleaning, combining and reshaping data in Excel and Power BI with repeatable steps that refresh automatically.

Is Power Query free?

Yes, it is built into Excel 2016 and later, Microsoft 365 and Power BI Desktop.

Is Power Query hard to learn?

No. Most tasks are menu clicks; M code is optional for advanced steps.

What is the difference between Power Query and Power Pivot?

Power Query prepares and cleans data; Power Pivot models it and calculates measures with DAX.

AN

Written by

Asim Noaman Lodhi

Certified Google Partner · QA Consultant · 12+ Years IT

QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.

4.5 Rating 913+ Students Google Partner 79 Lectures