Understanding Excel Power Query

Chapter 1: Introduction to Excel Power Query

In the modern world of data management and analysis, the need for efficient, repeatable,
and scalable data transformation has never been greater. Microsoft Excel, a cornerstone of
data handling for millions of users worldwide, evolved to meet this demand with the
introduction of Power Query—a robust tool designed to simplify and automate the
Extract, Transform, Load (ETL) process.

The Emergence of Power Query

Power Query first appeared as an external add-in for Excel 2010 and 2013, requiring users
to download and install it separately. Recognizing its utility and widespread demand,
Microsoft integrated Power Query as a built-in feature in Excel 2016, placing it under the
"Get & Transform" section of the Data tab. Since then, it has become an essential part of
Excel’s data toolkit.

With the rise of Office 365 and Microsoft 365 subscriptions, Power Query continues to
receive regular updates, introducing new features and improvements based on user
feedback. These updates ensure that users always have access to the latest capabilities for
managing complex data tasks.

Why Power Query Is Essential

At the heart of Power Query lies its ability to streamline the ETL process:

1. Extract: Pull data from multiple and diverse sources—Excel workbooks, CSV files,
databases, web pages, SharePoint folders, cloud services like Salesforce, and many
more.
2. Transform: Clean and reshape data to fit analytical needs. This includes removing
duplicates, filtering rows, changing data types, splitting columns, removing blanks,
and pivoting or unpivoting data.
3. Load: Output the refined data into Excel worksheets or data models, ready for
reporting, pivot tables, dashboards, and further analysis.

This process, once automated, requires no repetition. After setting up a Power Query
solution once, users only need to refresh the data to incorporate new entries or updates—
saving hours of manual work and reducing the risk of human error.
A Practical Scenario

Consider the case of a high school administrator responsible for compiling student
performance reports across different subjects like Mathematics, Chemistry, and Physics.
Each month or quarter, teachers submit Excel files with student grades. Traditionally, this
process involves manually opening each file, copying data into a master sheet, and then
preparing reports from there.

With Power Query, the process becomes seamless. The administrator can connect to a
folder where teachers drop their files, configure Power Query to combine all these datasets,
apply necessary transformations (such as removing empty rows, renaming columns, or
filtering by subject), and load the cleaned data into a consolidated report. From that point
forward, all it takes is one click to refresh the data—and the entire report updates
instantly.

Beyond Efficiency: The Broader Value of Power Query

The advantages of Power Query go far beyond automation:

• Consistency: Once defined, transformation rules apply uniformly across all

datasets, ensuring consistency in reports.
• Reproducibility: Every step in the query process is recorded and can be audited or
modified later.
• Data Integration: Power Query allows seamless integration of data from multiple
formats and platforms, removing barriers between departments or systems.
• Error Reduction: By automating repetitive tasks, Power Query reduces the
likelihood of manual errors in data handling.
• Scalability: Whether working with a handful of files or thousands of records from
various sources, Power Query handles data with impressive scalability.
Chapter 2: Installing the Power Query Add-In for Excel 2010 and 2013

Power Query is a powerful tool for working with data in Excel. If you’re using Excel 2016
or later, you already have Power Query built into Excel under the Data tab, where it is
called Get & Transform Data. There’s no need to install anything separately in that case.

However, if you’re using Excel 2010 or Excel 2013, Power Query does not come pre-
installed. You will need to download and install it manually as an add-in. This chapter will
guide you through the steps to do that.

Step-by-Step Installation Guide

1. Check Your Excel Version (32-bit or 64-bit)

Before downloading Power Query, you need to find out whether your Excel is 32-bit or 64-
bit.

• Open Excel.
• Click on the File tab.
• Go to Account (or Help in some versions).
• Click on About Excel.
• A window will appear showing whether your version is 32-bit or 64-bit.

Make a note of this. You’ll need it when choosing which file to download.

2. Download the Power Query Add-In

• Open your web browser.

• Search for: "Download Power Query add-in".
• Click the link that takes you to the official Microsoft website.
• On the download page, click the Download button.
• You will be asked to select a version—choose 32-bit or 64-bit depending on what
you found earlier.
• Click Next and the file will begin downloading.

3. Install the Add-In

• Once the file is downloaded, double-click it to start the installation.

• Follow the on-screen instructions (they’re simple and clear).
 • When finished, close Excel if it’s open, and reopen it.

You should now see a new Power Query tab in your Excel ribbon.

4. If You Don’t See the Power Query Tab

Sometimes, even after installing, the Power Query tab doesn’t appear automatically. You’ll
need to activate it manually:

• Open Excel.
• Go to the File tab → Options.
• In the Excel Options dialog, click Add-Ins.
• At the bottom, in the Manage drop-down, choose COM Add-ins and click Go.
• In the list, check the box for Microsoft Power Query for Excel.
• Click OK, then restart Excel.

Now the Power Query tab should be visible and ready to use.

Power Query Add-In vs. Get & Transform

If you're comparing the Power Query add-in with the built-in Get & Transform in Excel
2016 and later, the core features are very similar. The main difference is where the options
appear—they may be placed in slightly different tabs or groups. However, the functionality
is almost the same, so you can follow the same steps regardless of your version.

Even if you're using Excel 2010 or 2013, you'll still be able to work through all the exercises
in this course or book without any problems.
Chapter 3: Overview of Query Editor in Power Query
Introduction to Power Query Editor in Excel

In Excel 2016 and later, Power Query is integrated under the Data tab and labeled as Get &
Transform. However, in Excel 2010 and 2013, it must be installed as an add-in, after which
it appears as a separate Power Query tab.

Accessing Power Query

To launch Power Query:

- Go to the Data tab (Excel 2016+)

- Select Get Data to connect to a wide range of data sources such as:

o Excel workbooks
o Text/CSV files
o XML or JSON files
o Databases
o Web sources
o Online services
o Blank queries

Note: While the UI elements and exact placement of these options may change in
future Excel updates, the core functionalities remain the same.

Common Data Sources

Frequently used data connection options include:

• From Text/CSV
• From Web
• From Table/Range

These options can also be accessed via the File and Other Sources sections within the Get
Data dropdown.

Exploring Recent and Existing Connections

• Recent Sources: View and access your most recently used data connections.
• Existing Connections: Reuse previously connected datasets.
• Queries & Connections Pane: View all active queries and data connections in the
current workbook.
Launching the Query Editor

To open the Query Editor:

• Navigate to From Other Sources → Blank Query

• This opens the Power Query Editor, where all data transformations take place.

When data is loaded from a source (e.g., CSV or Excel), it appears in the central pane of the
Query Editor for manipulation.

Interface Overview: Power Query Editor

The Query Editor contains a ribbon similar to Excel, with the following key tabs:

1. File

• Options to Close & Load the query.

• Load data to:
o Excel worksheet
o Connection only
o Power Pivot data model

2. Home

• Common actions like:

o Close & Load
o Remove Columns
o Keep Rows
o Split Columns
o Group By
o Sort and Filter

3. Transform

• Tools for transforming your data:

o Remove duplicates, errors, or blanks
o Change data types
o Use first row as headers

4. Add Column

• Add new columns using custom rules or formulas.

• Generate new data columns based on existing columns.
5. View

• Toggle display of the Formula Bar, Query Settings, and Advanced Editor.

Understanding Query Settings and Applied Steps

Located on the right-hand side, the Query Settings pane is critical to working with Power
Query:

• Query Name: Assign a custom name to the query (avoid spaces).

• Applied Steps: Every transformation or change made is recorded here in sequence.

Important: Power Query acts like a step recorder, capturing each transformation. This
makes your process automatable. Once steps are defined, simply updating the source and
refreshing the query will apply the same transformations to the new data.

Closing the Query Editor

To finalize your work:

• Go to File → Close & Load to load the transformed data into Excel.
• Alternatively, you can discard changes if no transformation was done.
Chapter 4 - Import Data from TEXT Files in Excel using Power Query

Power Query in Excel makes importing data from text files both efficient and dynamic.
Before Power Query existed, importing text file data required manually opening the file and
walking through the Text Import Wizard every time. This method had to be repeated with
each new data file—making recurring data analysis repetitive and error-prone.

Traditional Method: Manual Import

Previously, to import a text file, you would:

1. Go to File > Open > Browse and locate the text file (e.g., Sales Data March).
2. Excel would launch the Text Import Wizard, prompting you to select delimiters, data
formats, etc.
3. After import, any data transformations—like splitting or formatting—had to be
done manually in Excel.
4. These transformations would need to be repeated each time new monthly data
arrived.

Using Power Query Instead

Power Query eliminates the need to repeat steps by allowing data transformation to be
saved and reused.

Step-by-Step: Importing a Text File

1. Open a new Excel workbook.

2. Go to the Data tab.
3. Click From Text/CSV.
4. Browse to select your text file (e.g., Sales Data March) and click Import.
5. Excel will show a preview and detect the delimiter (e.g., Tab). You can change the
delimiter if needed.
6. Click Transform Data to open the Power Query Editor.

Editing the Data in Power Query

In the Power Query Editor:

• Power Query automatically applies steps such as promoting headers and changing
data types.
 • You can modify or delete these applied steps as needed.

Example: Clean Product ID

If the Product ID contains a string like PR001, and you want only the numeric part:

1. Select the Product ID column.

2. Right-click > Split Column > By Number of Characters.
3. Set it to split after 2 characters (separating "PR" from the numbers).
4. Remove the column containing "PR".
5. Rename the remaining column (e.g., Product ID).

Loading the Clean Data

1. After transformations, click File > Close & Load.

2. Power Query loads the cleaned data into Excel as a table, named after the query.
3. This table remains linked to the source file.

Reusing the Query for New Monthly Data

To update for the next month (e.g., April):

1. Double-click the query name in the Queries pane to reopen Power Query Editor.
2. In the Source step, change the file path from Sales Data March to Sales Data April.
3. Click OK.
4. Power Query re-applies all previous transformation steps automatically to the new
file.
5. Click Close & Load to refresh the table in Excel.

Refreshing When Source File is Updated

If the current source file (e.g., Sales Data April) is updated:

• Simply right-click the query in Excel and select Refresh.

• Or, go to the Query tab > Refresh.

Any connected PivotTables will also update when refreshed.

Chapter 5: Import Data from CSV Files into Excel Using Power Query

In this Chapter, you’ll learn how to import data from a CSV file into Excel using Power
Query, apply basic transformations, and set up automatic refresh whenever the source file
is updated. This helps you save time and keep your data analysis clean and consistent.

🔹 Step 1: Locate and Import Your CSV File

1. Go to the Data tab in Excel.

2. Click on From Text/CSV.
3. In the dialog box that opens, locate your CSV file.
For example: Sales Data - [Link].
4. Click Import.

A preview window will open showing the first 200 rows of your data. This helps you
confirm the structure and delimiter (usually commas for CSV files).

Note: This is not yet the Power Query Editor—just a preview.

🔹 Step 2: Open Power Query to Edit Your Data

Instead of loading the data directly into Excel, let’s clean it up first.

1. Click Transform Data (or Edit in older versions).

2. The Power Query Editor will open.
3. You’ll see a list of applied steps on the right:
o Source: The CSV file itself.
o Promoted Headers: Power Query detects that the first row contains
column names.
o Changed Type: Power Query guesses data types (dates, numbers, text, etc.).

These steps are automatic but can be modified or deleted if needed.

🔹 Step 3: Clean the “Product Id” Column

Let’s say the Product Id column contains both letters and numbers (e.g., “PR001”, “TX45”).
We want to remove the letters and keep only the numbers.
Here’s how to do it:

1. Right-click on the Product Id column.

2. Choose Split Column → By Number of Characters.
3. Enter 2 (if the first two characters are letters).
4. Click OK.

Now the column is split into two:

• One with the letters

• One with the numbers

Delete the column with letters, and rename the other to “Product Id”.

🔹 Step 4: Load the Data into Excel

Once your data is cleaned:

1. Click Close & Load (top-left in the Query Editor).

2. The data will be added as a table in Excel.
3. The table will have the same name as your query (you can rename it if you want).

🔹 Step 5: Reuse the Same Process for Other Files (Like April Data)

If your data is updated every month (e.g., Sales Data - [Link]), you don’t have to repeat all
the steps.

Here’s what you do:

1. In Excel, double-click the query name to reopen Power Query.

2. In the Source step, click on the file path.
3. Select the new file (e.g., Sales Data - [Link]) and click OK.
4. Power Query will apply all your previous steps automatically!
5. Click Close & Load.

You’ll now see the new data with no extra work.

🔹 Step 6: Refresh Automatically When Source Changes

If the data inside your connected CSV file changes (e.g., updated sales values), just:

1. Right-click on the Excel table.

2. Select Refresh.

Power Query will go back to the CSV file, reapply the steps, and update the table instantly.

Why This Is Powerful

• You only clean and set up your data once.

• New data files can be updated with a few clicks.
• You avoid doing repetitive formatting every time.
• It keeps your data analysis fast, consistent, and automated.

Bonus Tip

To avoid breaking the link, don’t move or rename the source CSV file after connecting it
with Power Query.
Chapter 6: Import Data from Another Excel Workbook Using Power Query

Power Query makes it easy to pull data from one Excel file into another. Instead of copy-
pasting data manually every time, Power Query lets you connect the files. Once
connected, any updates made in the original file can automatically reflect in your current
workbook with just a click.

🔹 Scenario

Let’s say you have:

• A file called Sales Data - March [Link] that contains your raw data.
• A new workbook where you want to bring in that data automatically.

You’ll learn how to bring in this data, clean it if needed, and set it up to update easily when
changes are made in the original file.

🔹 Step 1: Get Data from Another Workbook

1. Open a new or existing workbook (the one where you want to import the data).
2. Go to the Data tab.
3. Click on Get Data → From File → From Workbook.
4. Browse to find the file (e.g., Sales Data - March [Link]) and click Import.

A Navigator window will open, showing available objects like worksheets or tables from
the selected file.

🔹 Step 2: Select What to Import

You will see options like:

- Sheet1

- Any named tables, if they exist

If the data source is not in a table format, it may include extra rows like titles or blank rows.
Let’s fix that.
Click on Sheet1 (or your specific sheet name) to preview the data.

You might see:

- Title rows
- Blank rows
- Incorrect headers

🔹 Step 3: Open Power Query Editor

Click on Transform Data to clean the data before loading.

You’ll now enter the Power Query Editor.

Power Query may automatically apply some steps:

• Navigation: Selecting the sheet

• Changed Type: Assigning data types (e.g., text, number)

🔹 Step 4: Clean the Data

1. Remove Top Rows

If there are extra rows (like titles or blank lines), go to:
o Home → Remove Rows → Remove Top Rows
o Type the number of rows to remove (e.g., 2) and click OK.
2. Use First Row as Headers
Go to:
o Transform → Use First Row as Headers
3. Check and Set Data Types
For each column, make sure the type is correct:
o Text (for names, items, etc.)
o Whole Number (for quantities)
o Date (for date columns)
o Percentage or Decimal (for prices or commission)

To change the type:

• Click on the column

• Go to the Transform tab
• Select the correct type from the Data Type dropdown
🔹 Step 5: Load the Cleaned Data into Excel

Once your data is ready:

1. Go to Home in the Power Query Editor.

2. Click Close & Load.

The cleaned data will appear in Excel as a table. The name of the table will match the name
of the query (which you can change).

🔹 Step 6: Auto-Update When Data Changes

If you make a change in the original Excel file (e.g., changing names or values), you don’t
need to re-import.

1. Go back to the current workbook.

2. Right-click anywhere in the table.
3. Click Refresh.

Power Query will re-import the data, apply the same cleaning steps, and update your table.

Bonus: Use Tables in Source Workbook

If the data in the source file is already formatted as a table, Power Query can skip some
cleaning steps.

To create a table:

1. Open the source file.

2. Select the dataset.
3. Press Ctrl + T.
4. Give the table a name, like SalesData.

Now when you import:

• Go to Get Data → From File → From Workbook

• You will see both:
o Sheet1
o SalesData (the table)
Choose the table for a cleaner import:

• No need to remove extra rows

• Headers and data types are automatically detected

Example Use Case

Let’s say the original sales file is updated with new staff names:

1. In the source file, you change some names to “John”.

2. Save the file.
3. In your main workbook, just click Refresh.
4. The updated names will appear — no extra work needed.
Chapter 7: Getting Data from the Current Workbook into Power Query

In this lesson, you’ll learn how to import data from within your current Excel workbook into
Power Query using three different methods:

1. Plain Table (no formatting)

2. Excel Table
3. Named Range

Why This Matters

Power Query helps you clean, reshape, and prepare data efficiently. But before you can
use it, your data must be brought into the Power Query Editor — and how you structure
your data in Excel affects this process.

Example: Cleaning Supply Sales for March 2024

We have the same dataset available on three different sheets:

• Sheet1: Plain tabular data (no formatting).

• Sheet2: Data formatted as an Excel Table.
• Sheet3: Data saved as a Named Range.

Each sheet contains columns like:

• Date
• Product
• Category
• Quantity Sold
• Unit Price
• Total Revenue
Case 1: Importing from Plain Data (Sheet1)

When your data is just plain tabular data (not formatted as a table or named range), Power
Query will ask you to convert it into an Excel Table before importing.

Steps:

1. Go to Data tab.
2. Click From Table/Range.
3. Excel will ask to convert the data to a table. Click OK.
4. Power Query will open, and your data appears with default table name (e.g., Table2).

Note:

• Power Query detects column headers and assigns the right data types (e.g., Date,
Text, Number).
• This method is helpful, but you must format the data as a table first.

Case 2: Importing from an Excel Table (Sheet2)

If your data is already formatted as a Table in Excel, importing it into Power Query is even
easier.

Steps:

1. Click anywhere inside the table.

2. Go to Data > From Table/Range.
3. Power Query opens directly — no prompts or extra steps.

Note:

• Headers and data types are correctly detected immediately.

• The query takes fewer steps and is more efficient.

Case 3: Importing from a Named Range (Sheet3)

You can also use a Named Range (a selected set of cells you’ve given a name, e.g.,
SalesData) to load data into Power Query.
Steps:

1. Select any cell within the named range.

2. Go to Data > From Table/Range.
3. Power Query opens and loads the data.

Key Differences:

• Power Query doesn’t immediately know where the headers are.

• It loads all the data including the header row as the first data row.
• You will see three steps instead of two:
o Source
o Promoted Headers
o Changed Type

Note:

• You may need to manually promote the header row and assign correct data types.

Best Practice

For most cases, using an Excel Table is the recommended way to bring data into Power
Query:

• Headers are easily recognized.

• Data types are correctly assigned.
• The process is smoother and cleaner.
Chapter 8: Combine Excel Tables in the Same Workbook Using Power Query

Method Used: Append Queries

In this lesson, you will learn how to combine multiple Excel tables (from different sheets)
into one single table using Power Query's Append feature. This method is very useful
when you regularly receive similar data (e.g., monthly sales from different regions) and
want to avoid copying and pasting manually every time.

Scenario

Imagine you have four sheets in your workbook:

• East
• West
• North
• South

Each sheet contains sales data for March 2024, formatted as Excel Tables.

Your task is to combine all four tables into one consolidated table using Power Query.
Once set up, you’ll only need to refresh the data when new data is added each month.

Step-by-Step Instructions
Step 1: Load Each Table into Power Query

Do the following for each sheet (East, West, North, South):

1. Click anywhere inside the table.

2. Go to the Data tab.
3. Click From Table/Range.
4. The table opens in the Power Query Editor.

Step 2: Add a Region Column

Since each table doesn't indicate its region inside the data, you should add a new column to
label the region.

1. In the Power Query Editor, go to Add Column → Custom Column.

 2. Enter the name: Region.
3. In the formula box, type the region name in double quotes (e.g., "East").
4. Click OK.
5. Drag the new Region column to the first position if desired.

Repeat this step for each table, using the appropriate region name (West, North, or South).

Step 3: Save Each Table as a Connection

1. Go to File → Close & Load To.

2. Choose Only Create Connection.
3. Click OK.

Repeat this for all four regions. Now, all tables are stored as connections, not visible in the
workbook but ready for use.

Step 4: Append the Tables

Now let’s combine (append) all the connections into one table.

1. Go to the Data tab.

2. Click Get Data → Combine Queries → Append.
3. In the Append Queries dialog, choose Three or more tables.
4. Select all the region connections (East_Data, West_Data, etc.) and click Add.
5. Click OK.

The combined data opens in Power Query Editor. You will now see all rows from each
region, with a new Region column showing where each record came from.

Optional: Tidy Up

You may wish to:

• Change Date/Time columns to Date only.

• Rename or reorder columns for clarity.

You can do all this inside the Query Editor before loading the final result.
Step 5: Load the Combined Table to Excel

1. Go to File → Close & Load To.

2. Choose Table and select a new worksheet.
3. Click OK.

You now have one combined table with data from all regions.

Refreshing for New Data (e.g. April 2024)

When you receive new data next month:

1. Paste the new records in the corresponding regional sheets (e.g., East, West, etc.).
2. Go to your combined data sheet.
3. Click Refresh.

Power Query will update the consolidated report with the new entries — no manual
copying or pasting needed.

Important: Column Headers Must Match

All tables must have the same column headers and column order.

For example, if one table uses Sale Date and another uses Date, Power Query will throw
an error.

Fixing Mismatched Headers

If you get an error:

1. Open the individual query (e.g., West_Data) in Power Query.

2. Go to the Source step.
3. Rename the mismatched column using the Rename Columns step (or update the
header name in Excel).
4. Click Close & Load To and keep the changes.

Once headers are consistent, the append will work without errors.
Chapter 9: Combine Excel Tables in the Same Workbook Using Power Query

(Formula Method)

This method is ideal when all your data is stored in separate tables within the same file.
Instead of importing files or using the Append Queries method, we will use a Power Query
formula to identify and consolidate tables efficiently.

Scenario Example

You have sales data for four regions: East, West, North, and South. Each region's data is
stored in a separate table named East_Data, West_Data, North_Data, and South_Data.
Your goal is to combine these into a single table.

Step-by-Step Guide

1. Start a Blank Query

o Go to the Data tab in Excel.
o Click Get Data > From Other Sources > Blank Query.
2. Enable the Formula Bar
o If the formula bar isn’t visible, go to the View tab in Power Query and check
Formula Bar.
3. Use [Link] Formula
o In the formula bar, type:

=[Link]()

o Press Enter.
o This formula retrieves all named ranges, tables, and connections in the
current workbook.
4. Identify the Tables
o A table will appear with columns like Name and Content.
o The Name column contains the names of the tables (e.g., East_Data).
o The Content column holds the actual table data.
5. Filter Only the Required Tables
o Click the dropdown in the Name column.
o Choose Text Filters > Ends With and enter _Data.
o This ensures only the tables you want (e.g., East_Data, West_Data) are
included.
6. Combine the Tables
o Click the double-arrow icon (⇄) at the top of the Content column.
 o Uncheck Use original column name as prefix.
o Click OK.
7. Clean the Region Names
o You now have a Name column (e.g., East_Data).
o Right-click the column > Replace Values.
o Replace _Data with an empty string to get clean region names.
o Rename the column to Region.
8. Set Data Types
o Ensure each column has the correct data type:
▪ Date column: Date
▪ Sales Rep, Item: Text
▪ Quantity, Price: Whole Number
▪ Commission: Decimal Number or Percentage
9. Load the Data
o Go to Home > Close & Load.
o This loads the combined table into a new worksheet.

Avoiding Errors (Important Note)

If you don’t filter the _Data tables first, Power Query might include the newly created
combined table (Query1) in future refreshes. This causes recursion, leading to data
duplication.

To prevent this:

• Always filter by table name (e.g., only include names ending with _Data).
• Use consistent naming conventions for all source tables.

Summary

Using [Link]() is a powerful and flexible way to combine multiple

tables inside the same workbook. It allows you to quickly consolidate data without having
to create multiple connections. As long as your table names follow a consistent pattern, this
method is both clean and dynamic.

This approach is perfect for course materials, reporting dashboards, and regular business
processes involving recurring data layouts.
Chapter 10: Combine Tables from Different Workbooks into One Table Using
Power Query

In this lesson, you’ll learn how to combine data from different Excel workbooks into a single
table using Power Query. This is helpful when you receive similar data from multiple
sources (e.g., branches, departments, or regions) in separate files and want to consolidate
them all into one report.

Scenario Example

You receive monthly sales data from four regions: East, West, North, and South. Each region
sends in its own Excel file containing a table with sales information.

Your goal is to combine these four files into one single table using Power Query.

Step-by-Step Instructions
Step 1: Set Up Your Workbook

1. Open a blank Excel workbook.

2. Go to the Data tab on the Ribbon.
3. Click Get Data > From File > From Workbook.
4. Browse to the first regional file (e.g., East [Link]) and click Import.

Step 2: Select the Table

Once the Navigator pane opens:

• Select the named table inside the workbook (e.g., East_Data).

• Avoid selecting the sheet unless you don’t have tables, as sheets may require extra
cleanup.
• Click Transform Data to open the Power Query Editor.

Step 3: Add a Region Column

To know which region each row belongs to:

1. In Power Query, go to Add Column > Custom Column.

2. Name the new column Region.
3. In the formula box, type the region name in quotes, e.g., = "East"
4. Click OK.
 5. Drag this column to the beginning of the table for clarity.
6. Click Home > Close & Load To… > Only Create Connection.

This creates a query connection without loading the data into the worksheet.

Step 4: Repeat for Other Workbooks

Repeat Steps 1 to 3 for the other files:

• North [Link] → Region: North

• South [Link] → Region: South
• West [Link] → Region: West

Each one should be connected using “Only Create Connection.”

Step 5: Append All Queries Together

Now that all four queries are created:

1. Go to Data > Get Data > Combine Queries > Append.

2. In the dialog box:
o Choose Three or more tables.
o Select all four query connections (East, West, North, South).
o Click Add, then OK.

The combined data appears in a new Power Query Editor window.

3. Rename the query to Consolidated_Data (optional).

Step 6: Check Data Types

Before loading:

• Check that columns like Name, Product, and Region are Text.
• Check that numeric columns like Quantity or Sales are Whole Number or Decimal
Number.
• Change any incorrect data types from the column header dropdown.

Step 7: Load Final Table

1. Go to Home > Close & Load To…

2. Choose Table, and load to a new worksheet.

You’ll now see all your data from the four regions combined into one Excel table.
Refreshing with New Monthly Data

If next month you receive a new file for East region:

1. Open the new file.

2. Replace the old data (in the original East [Link]) with the new one.
3. Save and close the file.
4. Go to Excel where the consolidated table is, and click Refresh All in the Data tab.

The updated data will reflect in your main table automatically.

If the File Name or Location Changes

If the new file is saved with a different name or location:

1. In the Power Query Editor, open the query for that region (e.g., East).
2. Click the gear icon next to the Source step.
3. Browse to the new file location and select the new file.
4. Click OK to update the source.

Benefits of Using Power Query for This

• Automation: Easily update the full report when new data comes in.
• Consistency: Structure remains the same every month.
• Scalability: Add more regions or files without rebuilding your report.
• No VBA or coding required.

This method is especially useful in organizations where reports are compiled monthly from
multiple branches or departments. Power Query keeps your workflow clean, organized, and
easily refreshable.