Excel Power Query

07/02/2025
 Power Query
Power Query (known as Get & Transform in Excel) allows you to import or connect
to external data and then shape that data—for example, by removing a column,
changing a data type, or merging tables—in ways that meet your needs. After
shaping the data, you can load your query into Excel to create charts and reports.
Periodically, you can refresh the data to keep it up to date. Power Query is available
on three Excel applications: Excel for Windows, Excel for Mac, and Excel for the
Web.

07/02/2025 2
 The Four Phases of Power Query

1. Connect
Make connections to data in the cloud, on a service, or locally.
2. Transform
Shape data to meet your needs while the original source remains unchanged.
3. Combine
Integrate data from multiple sources to get a unique view of the data.
4. Load
Complete your query and load it into a worksheet or Data Model, and periodically refresh it.

07/02/2025 3
 Connect

• You can use Power Query to import data from a single data
source, such as an Excel workbook, or from multiple
databases, feeds, or services scattered across the cloud.
Data sources include data from the Web, files, databases,
Azure, or even Excel tables in the current workbook. With
Power Query, you can then bring all those data sources
together using your own unique transformations and
combinations to uncover insights you otherwise wouldn’t
have seen.

• Once imported, you can refresh the data to bring in

additions, changes, and deletions from the external data
source.

07/02/2025 4
 Transform
• Transforming data means modifying it in some way to
meet your data analysis requirements. For example, you
can remove a column, change a data type, or filter rows.
Each of these operations is a data transformation. This
process of applying transformations (and combining) to
one or more sets of data is also called shaping data.

• Think of it this way: a vase starts as a lump of clay that one

shapes into something practical and beautiful. Data is the
same; it needs shaping into a table that is suitable for your
needs and enables attractive reports and dashboards.

• Power Query uses a dedicated window called the Power

Query Editor to facilitate and display data transformations.

• You can open the Power Query Editor by selecting Launch

Query Editor from the Get Data command in the Get &
Transform Data group, but it also opens when you connect
to a data source, create a new query, or load a query.
07/02/2025 5
 Combine

Append: An append operation creates a new query that contains all rows
from a first query followed by all rows from a second query. You can
perform two types of append operations:

•Intermediate Append: Creates a new query for each append operation.

•Inline Append: Appends data to your existing query until you reach a
final result.

Merge: A merge operation creates a new query from two existing queries.
This one query contains all columns from a primary table, with one
column serving as a navigation link to a related table. The related table
contains all rows that match each row from a common column value in
the primary table. Furthermore, you can expand or add columns from a
related table into the primary table.

07/02/2025 6
 Combine

Append: An append operation creates a new query that contains all rows
from a first query followed by all rows from a second query. You can
perform two types of append operations:

•Intermediate Append: Creates a new query for each append operation.

•Inline Append: Appends data to your existing query until you reach a
final result.

Merge: A merge operation creates a new query from two existing queries.
This one query contains all columns from a primary table, with one
column serving as a navigation link to a related table. The related table
contains all rows that match each row from a common column value in
the primary table. Furthermore, you can expand or add columns from a
related table into the primary table.

07/02/2025 7
 Load

There are two main ways to load queries into your workbook:

• From the Power Query Editor, you can use the Close and Load commands in the Close group on the Home tab.

• From the Excel Workbook Queries pane (Select Queries & Connections), you can right-click a query and select
Load To.

You can also fine-tune your load options by using the Query Options dialog box (Select File > Options and Settings >
Query Options) to choose how you want to view your data and where you want to load the data, either in a worksheet
or a Data Model (which is a relational data source of multiple tables that reside in a workbook).

07/02/2025 8
Create, load, or edit a query in
Excel (Power Query)

07/02/2025 9
 Create a Query
• You can either create a query from imported data or start with a blank query.

• Create a Query from Imported Data

• This is the most common way to create a query.

• Import some data.

• Select a cell in the data and then select Query > Edit.

• Create a Blank Query

You might want to start from scratch. There are two ways to do this:

• Select Data > Get Data > From Other Sources > Blank Query.
• Select Data > Get Data > Launch Power Query Editor.

• Select New Source to add a data source. This command is similar to the Data > Get Data command in the Excel ribbon.
• Select Recent Sources to choose from a data source you have been working with. This command mirrors the Data > Recent
Sources command in the Excel ribbon.
• Select Enter Data to manually enter data. You might choose this command to experiment with the Power Query Editor
independently of an external data source.

07/02/2025 10
 Load a Query
Assuming your query is valid and has no errors, you can load it back to a worksheet or Data Model.

Load a Query from the Power Query Editor

In the Power Query Editor, do one of the following:

•To load to a worksheet, select Home > Close & Load > Close & Load.
•To load to a Data Model, select Home > Close & Load > Close & Load To. In the Import Data dialog box, select Add this data to the
Data Model.

In the Import Data dialog box, select Add this data to the Data Model.

07/02/2025 11
 Load a Query
Load a Query from the Queries and Connections Pane

In Excel, you may want to load a query into another

worksheet or Data Model. Follow these steps:

1.Select Data > Queries & Connections, and then select

the Queries tab.

2.In the list of queries, locate the query, right-click on it,

and then select Load To. The Import Data dialog box
appears.

3.Decide how you want to import the data, and then

select OK. For more information about using this dialog
box, select the question mark (?).

07/02/2025 12
 Edit a Query from a Worksheet

To edit a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select
Query > Edit.
Edit a Query from the Queries & Connections Pane
You may find the Queries & Connections pane more convenient when you have many queries in one workbook and
want to quickly find one.
1. In Excel, select Data > Queries & Connections, and then select the Queries tab.
2. In the list of queries, locate the query, right-click on it, and then select Edit.
Edit a Query from the Query Properties Dialog Box
3. In Excel, select Data > Data & Connections > Queries tab, right-click on the query, and select Properties.
4. In the Properties dialog box, select the Definition tab, and then select Edit Query.

07/02/2025 13
 Common Errors to Avoid
• Mismatched Data Types:
• Tip: Verify and set correct data types for columns before transformation to avoid errors.
• Duplicate Column Names:
• Tip: Rename columns to prevent confusion and maintain clarity.
• Broken Queries:
• Tip: Refresh the query after edits to ensure all steps work correctly.
• Incorrect Step Order:
• Tip: Review and rearrange steps in the Applied Steps pane for logical flow.

07/02/2025 14
 Q&A

07/02/2025 14
 Task
• Task:
• Import sales data from multiple CSV files using Power Query.
• Clean the data by removing duplicates, fixing data types, and filling in missing
values.
• Merge the CSV files into a single dataset.
• Transform the dataset to include new calculated columns for total sales and
profit margin.
• Challenge:
• Set up the Power Query steps so that you can automate the entire cleaning
and transformation process for future data imports.

07/02/2025 16
 Thank You

07/02/2025 15