Power Query in Excel: A Complete Guide

How Power Query Works

Power Query follows a simple workflow that connects to data sources, transforms information, and loads the cleaned results into Excel for analysis.

Common Data Transformation Tasks in Power Query

Power Query in Excel covers a wide range of prep operations. These are the ones that Power Query for data analysts use most: 

• Remove duplicates – select a column, click Remove Duplicates
• Change data types – convert text to dates or numbers to currency
• Filter rows – drop blanks, outliers, or region-specific records
• Replace values – fix "USA" vs "U.S.A." inconsistencies at scale
• Split columns – break "First Last" into two fields by delimiter
• Pivot / Unpivot – reshape wide tables into long format
• Group by – aggregate rows by category with sum or average
• Custom columns – use M language in Power Query for calculated fields 

These work the same way regardless of your source, CSV, SQL, SharePoint, or web. That consistency is one of the core advantages of using Power Query in Excel over source-specific formula approaches.

Your Data Analytics Career is Around The Corner!Data Analyst CourseExplore Program

How to Import Data Using Power Query

Here's how to use Power Query in Excel for the three most common source types: 

From a File (Excel or CSV)

1. Go to Data → Get Data → From File, choose your type
2. Navigate to the file and click Import
3. Select your table or sheet in the Navigator
4. Click Transform Data to open the editor, or Load to import directly

From a Database

1. Go to Data → Get Data → From Database
2. Select your database type, enter credentials, and pick your table

From a Web Page

1. Go to Data → Get Data → From Other Sources → From Web
2. Paste the URL. Power Query in Excel automatically detects tables on the page.

When done transforming, click Close & Load. Power Query in Excel saves the entire connection, so future refreshes take seconds.

How to Merge and Append Queries

Follow these steps to merge and append queries

1. Go to Home → Merge Queries in the editor
2. Select the second query, click the matching column in each table
3. Choose a join type: Inner, Left Outer, Full Outer, etc.
4. Click OK, then expand the merged column

Append Queries (stacks rows from similar datasets):

1. Go to Home → Append Queries
2. Select two or more queries with the same structure and click OK

Use Merge to join related datasets in Power Query for Excel using a shared key. Use Append when consolidating files with identical structures, such as 12 monthly sales reports into a single table.

Become an Expert in Data AnalyticsWith Our Unique Data Analyst CourseExplore Program

Power Query Example: Cleaning a Sales Dataset

Here's a real Power Query project example showing how to clean data from a raw sales CSV file that contains blank rows, invalid data types, and a combined "Region – Rep Name" column.

• Load it: Data → Get Data → From Text/CSV
• Split the column: Transform → Split Column → By Delimiter (use "–") → rename to Region and Rep Name
• Fix sales column: change data type to Decimal Number
• Remove blanks: column dropdown → uncheck (null)
• Remove duplicates: Home → Remove Duplicates on the Order ID column
• Click Close & Load

Every step is saved. When next month's file arrives, swap the source and hit Refresh. Power Query in Excel automatically re-runs the full sequence. This is exactly how to automate reports using Power Query and why it's more practical than VBA for routine work.

For Power Query performance optimization, filter rows and remove unneeded columns early in your applied steps. This significantly reduces processing time for large datasets.

Ready to turn data into career growth? This Data Analyst roadmap outlines the skills, tools, salary trends, and advancement opportunities to help you become a successful Data Analyst.

Key Takeaways

• Power Query in Excel is located under the Data tab and requires no coding to start cleaning data
• The Power Query Editor logs every step in M language, making transformations fully repeatable
• Use Merge to join tables by key; use Append to stack rows from identically structured files

FAQs

1. Where is Power Query located in Excel?

In Office 2016 and Microsoft 365, it is located in Data → Get & Transform Data. For Excel 2010/2013, Microsoft officially discontinued support and removed download links for Power Query.

2. How do I clean data using Power Query?

Open your file in the Power Query Editor. Use Remove Duplicates, Replace Values, Change Type, Filter Rows, and Split Column. Each step is logged in Applied Steps and is fully editable. 

3. What is the Power Query Editor?

It's the dedicated workspace inside Excel Power Query, the ribbon tools. It has a live data preview and the Applied Steps pane that records every transformation you apply. 

4. How do I merge files in Power Query?

Go to Home → Merge Queries, select both queries, match the key column, and choose a join type. To stack rows instead, use Append Queries. 

5. Can Power Query replace Excel formulas?

For data preparation, largely yes. The difference between Power Query vs Excel formulas is about timing: Power Query cleans data before they are loaded into your sheet, and formulas calculate after. Power Query vs. VBA is a clear win, as almost all transformation tasks can be performed without any code. 

6. What data sources can Power Query connect to?

It can connect with more than 200 connector types for data in Excel, CSV, PDF, SQL Server, MySQL, Oracle, SharePoint, Azure, web pages, JSON, XML, and OData feeds. 

7. What is the difference between Power Query and Power Pivot?

Power Query is used to perform ETL, importing, and shaping data. Power Pivot manages modeling, relationships, and DAX calculations. Power Query vs Power BI is a different comparison: Power BI is built on Power Query and provides visualization on top of the same engine, along with cloud sharing.