Power Query for Data Import, Cleaning and Automation

Power Query is a data connection and transformation tool available in modern versions of Excel. It can import data from Excel workbooks, CSV files, folders, databases, websites and many other sources.

After connecting to a source, Power Query can remove unnecessary columns, correct data types, clean text, replace errors, split columns, combine tables and prepare the data for PivotTables, charts and dashboards. The complete process can then be repeated using Refresh instead of performing the same manual work every month.

In this chapter, you will learn how to import, clean, transform, append, merge, load and refresh business data using Power Query.

Learning Objectives

After completing this chapter, you should be able to:

• Understand the purpose of Power Query.
• Connect Excel to different data sources.
• Use the Power Query Editor.
• Correct column names and data types.
• Remove blanks, duplicates and errors.
• Split, merge and transform columns.
• Create conditional and custom columns.
• Append several tables vertically.
• Merge related tables using matching keys.
• Combine monthly files from a folder.
• Load results into Excel or the Data Model.
• Refresh an automated reporting process.

1. What Is Power Query?

Power Query is an ETL tool built into Excel. ETL means Extract, Transform and Load.

Extract
Connect to Excel, CSV, folder, web or database data
        ↓
Transform
Clean, filter, reshape and combine the data
        ↓
Load
Send the prepared result to Excel or the Data Model

Extract

Power Query connects to a source and reads the required data.

Transform

The data is cleaned and reorganized without manually changing the original source file.

Load

The prepared result is loaded into an Excel worksheet, PivotTable connection or Data Model.

2. Why Use Power Query?

Traditional Manual Process

Open monthly file
        ↓
Copy the data
        ↓
Paste into master workbook
        ↓
Delete blank rows
        ↓
Correct date formats
        ↓
Split combined columns
        ↓
Remove duplicates
        ↓
Repeat next month

Power Query Process

Connect once
        ↓
Define cleaning steps once
        ↓
Add new source data
        ↓
Click Refresh
        ↓
Updated report

Benefits of Power Query

• Reduces repetitive manual work.
• Records every transformation step.
• Preserves the original source data.
• Handles large datasets more efficiently than manual copying.
• Combines multiple files automatically.
• Improves consistency and repeatability.
• Connects to many external data sources.
• Supports refreshable reports and dashboards.

3. Power Query Availability in Excel

In modern Excel versions, Power Query is available through the Data tab under the Get & Transform Data section.

Common Commands

• Get Data
• From Text/CSV
• From Web
• From Table/Range
• Recent Sources
• Existing Connections
• Queries & Connections
• Refresh All

Available connectors and commands can vary according to the Excel version, operating system and Microsoft 365 subscription.

4. Power Query Editor Interface

The Power Query Editor opens when you choose to transform imported data.

Main Areas of the Editor

Area	Purpose
Ribbon	Contains transformation and query commands
Queries Pane	Displays all queries in the workbook
Data Preview	Displays a preview of the selected query
Query Settings	Displays the query name and applied steps
Formula Bar	Displays the M expression for the selected step
Status Bar	Displays column, row and profiling information

5. Understanding Applied Steps

Every transformation performed in Power Query is recorded in the Applied Steps section.

Example Applied Steps

Source
Navigation
Promoted Headers
Changed Type
Removed Blank Rows
Trimmed Text
Removed Duplicates
Filtered Rows
Added Custom Column

During refresh, Power Query repeats these steps in the same order against the latest source data.

Important Applied-Step Rules

• Steps are executed from top to bottom.
• Deleting an earlier step can affect later steps.
• Renaming a column may affect transformations that reference it.
• Use clear step names for complex queries.
• Review the result after modifying any existing step.

6. Importing Data from an Excel Table

Create a worksheet named Sales_Data and enter the following sample records:

Order ID	Order Date	Product ID	Region	Quantity	Unit Price	Status
ORD-1001	01-Jul-2026	P-101	East	2	45000	Completed
ORD-1002	02-Jul-2026	P-102	North	4	12000	Completed
ORD-1003	03-Jul-2026	P-103	West	10	1500	Pending
ORD-1004	04-Jul-2026	P-104	South	15	800	Completed

Load the Table into Power Query

1. Select any cell inside the data.
2. Press Ctrl + T to create an Excel Table.
3. Name the table SalesData.
4. Go to Data → From Table/Range.
5. The table opens inside the Power Query Editor.

7. Importing a CSV File

1. Go to Data → Get Data → From File → From Text/CSV.
2. Select the CSV file.
3. Review the file preview.
4. Check the delimiter and file origin.
5. Select Transform Data.
6. Clean and prepare the data inside Power Query.

Common CSV Problems

• Incorrect delimiter
• Incorrect character encoding
• Dates interpreted incorrectly
• Numbers imported as text
• Extra header or footer rows
• Empty columns

8. Importing Data from Another Excel Workbook

1. Go to Data → Get Data → From File → From Excel Workbook.
2. Select the source workbook.
3. Use the Navigator to select a sheet, table or named range.
4. Select Transform Data.
5. Apply the required cleaning steps.

Table vs Worksheet Import

Importing a properly defined Excel Table is usually more reliable than importing an entire worksheet. A worksheet may contain headings, totals, notes or blank areas outside the actual dataset.

9. Importing Data from the Web

1. Go to Data → Get Data → From Other Sources → From Web.
2. Enter the webpage URL.
3. Complete authentication if required.
4. Select the required table in the Navigator.
5. Select Transform Data.

Website structures can change, which may break an existing query. Only import data that you are authorized to access and reuse.

10. Renaming a Query

Every query should have a meaningful name.

Poor Query Names

Query1
Sheet1
Table1
Data

Professional Query Names

Sales_Transactions
Product_Master
Customer_Master
Monthly_Sales_Combined
Final_Sales_Report

Rename a query through the Query Settings pane or by right-clicking it in the Queries pane.

11. Promoting Headers

Imported data may display generic column names such as Column1 and Column2.

1. Go to the Home or Transform tab.
2. Select Use First Row as Headers.
3. Verify that every heading is correct and unique.

Remove Rows Before Promoting Headers

If the file contains title or report-information rows above the headings:

1. Select Home → Remove Rows → Remove Top Rows.
2. Enter the number of rows to remove.
3. Then select Use First Row as Headers.

12. Correcting Data Types

Every Power Query column has a data type. Correct types are essential for reliable calculations, sorting and relationships.

Common Data Types

• Text
• Whole Number
• Decimal Number
• Fixed Decimal Number
• Percentage
• Date
• Time
• Date/Time
• Duration
• True/False

Examples

Column	Recommended type
Order ID	Text
Order Date	Date
Quantity	Whole Number
Unit Price	Fixed Decimal or Decimal Number
Attendance	Percentage
Status	Text

Change a Data Type

1. Select the column.
2. Click the data-type icon in the column heading.
3. Select the correct type.
4. Review any resulting errors.

13. Using Change Type with Locale

Dates or numbers may follow a regional format different from the computer’s settings.

1. Right-click the column.
2. Select Change Type → Using Locale.
3. Select the required data type.
4. Select the source locale.
5. Click OK.

For example, use the appropriate locale when imported dates are written as day/month/year but Excel interprets them as month/day/year.

14. Removing Unnecessary Columns

Remove Selected Columns

1. Select the unnecessary columns.
2. Right-click and choose Remove Columns.

Keep Only Selected Columns

1. Select the required columns.
2. Right-click and choose Remove Other Columns.

Removing unnecessary columns reduces the query size and keeps the transformation focused.

15. Removing Rows

Available Row-Removal Options

• Remove Top Rows
• Remove Bottom Rows
• Remove Alternate Rows
• Remove Blank Rows
• Remove Duplicates
• Remove Errors

Remove Blank Rows

1. Go to Home → Remove Rows.
2. Select Remove Blank Rows.

16. Filtering Rows

Power Query filters operate similarly to Excel filters, but the filter becomes a repeatable transformation step.

Common Filter Examples

• Keep only Completed orders.
• Remove Cancelled records.
• Keep sales greater than ₹50,000.
• Keep orders from a selected date onward.
• Keep only nonblank Product IDs.

Remove Cancelled Orders

1. Open the Status filter.
2. Clear Cancelled.
3. Click OK.

17. Removing Duplicate Records

Power Query keeps the first occurrence based on the selected columns and removes later duplicates.

Remove Duplicate Order IDs

1. Select the Order ID column.
2. Go to Home → Remove Rows → Remove Duplicates.

Remove Duplicates Using Multiple Columns

1. Select Order ID and Product ID while holding Ctrl.
2. Select Remove Duplicates.

Choose the duplicate key carefully. Removing duplicates using the wrong columns may delete legitimate transactions.

18. Cleaning Text Columns

Power Query provides several text transformations under the Transform tab.

Useful Text Commands

• Lowercase
• Uppercase
• Capitalize Each Word
• Trim
• Clean
• Length
• Extract
• Parse

Clean Customer Names

1. Select the Customer Name column.
2. Select Transform → Format → Clean.
3. Select Transform → Format → Trim.
4. Select Capitalize Each Word if appropriate.

Standardize Email Addresses

1. Select the Email column.
2. Apply Trim.
3. Apply Clean.
4. Convert the text to lowercase.

19. Replacing Values

Standardize Region Names

Suppose the source contains East, EAST and E. These can be standardized as East.

1. Select the Region column.
2. Choose Transform → Replace Values.
3. Replace EAST with East.
4. Replace E with East.

Replace Missing Status Values

Null represents a missing value in Power Query. Replace null with a meaningful value only when the business rule supports it.

Replace:
null

With:
Unknown

20. Splitting Columns

Split Column separates combined text into multiple columns.

Split Product Code by Hyphen

Suppose the Product Code is LAP-EAST-2026.

1. Select Product Code.
2. Choose Transform → Split Column → By Delimiter.
3. Select the hyphen delimiter.
4. Split at each occurrence.
5. Rename the new columns Product, Region and Year.

Split Full Name by Space

Use the leftmost or rightmost delimiter when names contain more than two parts.

Split by Number of Characters

This is useful for fixed-length IDs and standardized codes.

Split into Rows

A comma-separated list can be split into separate rows instead of columns when each item should become a new record.

21. Merging Columns

Merge Columns combines selected columns using a delimiter.

Combine First Name and Last Name

1. Select First Name and Last Name.
2. Choose Transform → Merge Columns.
3. Select Space as the separator.
4. Name the result Full Name.

Create a Composite Key

Combine Order ID and Product ID using an underscore:

ORD-1001_P-101

Composite keys can help match records when one column alone is not unique.

22. Adding an Index Column

An Index Column generates sequential numbers.

1. Go to Add Column → Index Column.
2. Select From 0, From 1 or Custom.

Uses of an Index Column

• Create serial numbers.
• Preserve or restore row order.
• Create a technical row identifier.
• Support comparison with previous or next rows.

23. Adding a Conditional Column

A Conditional Column works like an IF formula.

Create a Sales Category

Condition	Result
Total Sales is at least 100000	High Value
Total Sales is at least 50000	Medium Value
Otherwise	Regular

Steps

1. Go to Add Column → Conditional Column.
2. Enter Sales Category as the column name.
3. Add the required conditions.
4. Click OK.

24. Adding a Custom Column

A Custom Column uses a Power Query M expression.

Calculate Total Sales

[Quantity] * [Unit Price]

Calculate Profit

[Total Sales] - [Cost]

Calculate Profit Percentage

if [Total Sales] = 0 then
    null
else
    ([Total Sales] - [Cost]) / [Total Sales]

Create a Status Message

if [Total Sales] >= 100000 then
    "High Value"
else if [Total Sales] >= 50000 then
    "Medium Value"
else
    "Regular"

Power Query M expressions are case-sensitive. Column names containing spaces are selected through the interface and may appear using special syntax automatically.

25. Working with Date Columns

Useful Date Transformations

• Year
• Quarter
• Month
• Month Name
• Week
• Day
• Day Name
• Age
• Start of Month
• End of Month

Add a Year Column

1. Select Order Date.
2. Go to Add Column → Date → Year → Year.

Add a Month Name

1. Select Order Date.
2. Go to Add Column → Date → Month → Name of Month.

Add a Quarter

1. Select Order Date.
2. Choose Add Column → Date → Quarter → Quarter of Year.

26. Pivoting Data

Pivot Column converts unique values from one column into separate columns.

Example Source

Region	Month	Sales
East	January	100000
East	February	120000
West	January	90000

Pivoted Result

Region	January	February
East	100000	120000
West	90000	Null

27. Unpivoting Data

Unpivot converts several heading columns into attribute-and-value rows. This is useful when monthly data is arranged horizontally.

Original Format

Product	January	February	March
Laptop	100000	120000	135000

Unpivoted Format

Product	Month	Sales
Laptop	January	100000
Laptop	February	120000
Laptop	March	135000

Steps to Unpivot

1. Select the Product column.
2. Right-click and select Unpivot Other Columns.
3. Rename Attribute to Month.
4. Rename Value to Sales.
5. Set the correct data types.

28. Grouping Data

Group By summarizes rows according to one or more columns.

Calculate Sales by Region

1. Select Home → Group By.
2. Group by Region.
3. Name the new column Total Sales.
4. Select Sum.
5. Select the Total Sales source column.

Advanced Grouping

Use Advanced mode to calculate several results:

• Total Sales
• Order Count
• Average Sales
• Minimum Sale
• Maximum Sale

29. Understanding Append Queries

Append adds the rows of one table below another table. It is similar to placing one dataset under another.

January Sales
       +
February Sales
       +
March Sales
       =
Combined Sales Table

When to Use Append

• Combine monthly transaction tables.
• Combine regional sales files.
• Combine branch-level reports.
• Combine historical and current records.

30. Append Two Queries

1. Import the January and February tables as separate queries.
2. Open Power Query Editor.
3. Go to Home → Append Queries.
4. Select Append Queries as New.
5. Select the January and February queries.
6. Click OK.
7. Rename the new query Combined_Sales.

Append Three or More Queries

1. Select Three or more tables.
2. Add each required query.
3. Arrange them if necessary.
4. Click OK.

Important Append Requirement

Tables should use consistent column names and compatible data types. When columns do not match, Power Query creates separate columns and inserts null values where data is unavailable.

31. Understanding Merge Queries

Merge joins two tables horizontally based on matching values. It is similar to XLOOKUP, VLOOKUP or a database join.

Sales Transactions
Product ID: P-101
        +
Product Master
Product ID: P-101
        =
Sales with Product Name, Category and Cost

When to Use Merge

• Add product details to sales records.
• Add customer information to invoices.
• Add department information to employees.
• Compare orders with payment records.
• Identify unmatched values between systems.

32. Create the Product Master Table

ProductID	ProductName	Category	StandardCost
P-101	Laptop	Computer	38000
P-102	Monitor	Computer	9500
P-103	Keyboard	Accessories	950
P-104	Mouse	Accessories	500

Convert it into an Excel Table named ProductData and load it into Power Query.

33. Merge Sales and Product Queries

1. Select the Sales query.
2. Go to Home → Merge Queries.
3. Select ProductData as the second query.
4. Select Product ID in both tables.
5. Select Left Outer as the join type.
6. Click OK.
7. Expand the new ProductData column.
8. Select Product Name, Category and Standard Cost.
9. Clear the prefix option if shorter headings are preferred.

34. Understanding Join Types

Join type	Result
Left Outer	All rows from the first table and matching rows from the second
Right Outer	All rows from the second table and matching rows from the first
Full Outer	All rows from both tables
Inner	Only rows matching in both tables
Left Anti	Rows from the first table with no match in the second
Right Anti	Rows from the second table with no match in the first

Most Common Business Join

Left Outer is frequently used when the complete transaction table must be preserved while related master information is added.

Find Invalid Product IDs

Use Left Anti to return sales records whose Product IDs do not exist in the Product Master table.

35. Merging on Multiple Columns

Sometimes one column is not enough to identify a record uniquely.

Example Matching Columns

• Order ID and Product ID
• Employee ID and Month
• Customer ID and Invoice Number
• School ID and Academic Year

Steps

1. Open the Merge dialog.
2. Hold Ctrl and select the matching columns in the first table.
3. Select the corresponding columns in the same order in the second table.
4. Select the join type.
5. Click OK.

36. Combining Files from a Folder

The Folder connector can combine many files with the same basic structure into one table.

Example Folder

Monthly_Sales
├── Sales_January.xlsx
├── Sales_February.xlsx
├── Sales_March.xlsx
├── Sales_April.xlsx
└── Sales_May.xlsx

Preparation Rules

• Keep files in one dedicated folder.
• Use consistent column headings.
• Use compatible data types.
• Keep the required table or sheet structure consistent.
• Do not place unrelated files in the folder.
• Avoid temporary files beginning with ~$.

37. Import and Combine Folder Files

1. Go to Data → Get Data → From File → From Folder.
2. Select the monthly sales folder.
3. Review the file list.
4. Select Combine & Transform Data.
5. Select the correct sample table or worksheet.
6. Review the automatically created helper queries.
7. Apply the required cleaning steps to the sample-file transformation.
8. Review the final combined query.
9. Load the result into Excel or the Data Model.

What Happens During Refresh?

When a new monthly file with the expected structure is added to the folder, refreshing the query can include it automatically.

38. Filter the Folder File List

Before combining files, filter the folder query to exclude unwanted files.

Recommended Filters

• Keep only .xlsx or .csv files.
• Exclude hidden files.
• Exclude temporary files.
• Keep filenames beginning with Sales_.
• Exclude archive or backup folders.

39. Handling Errors

Replace Errors

1. Select the column containing errors.
2. Go to Transform → Replace Errors.
3. Enter a suitable replacement.

Remove Error Rows

1. Select the relevant columns.
2. Choose Home → Remove Rows → Remove Errors.

Recommended Approach

Investigate why errors occur before removing or replacing them. Errors may identify invalid dates, incorrect numerical values or unexpected source-file changes.

40. Handling Null Values

Power Query uses null to represent a missing value. Null is different from an empty text string.

Possible Treatments

• Keep null when the value is genuinely unknown.
• Replace null with zero for a valid numerical business rule.
• Replace null with “Unknown” for a missing category.
• Filter null records into an exception report.
• Remove rows only when they are genuinely unusable.

41. Fill Down and Fill Up

Fill Down copies the previous nonblank value into the blank rows below it. Fill Up copies the next nonblank value upward.

Example Source

Department	Employee
Sales	Rahul
Null	Priya
Marketing	Imran
Null	Neha

Fill Down Result

Department	Employee
Sales	Rahul
Sales	Priya
Marketing	Imran
Marketing	Neha

42. Query Reference vs Duplicate

Duplicate

Duplicate creates a separate copy of a query and its existing transformation steps.

Reference

Reference creates a new query based on the final result of another query.

Recommended Use

• Use Reference to create different reports from one cleaned base query.
• Use Duplicate when you need an independent copy of the transformation process.

43. Staging Queries

A staging query is used to connect and clean source data but is not loaded directly into a worksheet.

Raw Sales Query
Connection Only
        ↓
Clean Sales Query
Connection Only
        ↓
Sales Summary Query
Loaded to worksheet

Benefits

• Reduces duplicate transformation work.
• Keeps the workbook organized.
• Supports reusable base queries.
• Avoids loading unnecessary intermediate tables.

44. Close and Load Options

Close & Load

Loads the query into a new worksheet using default settings.

Close & Load To

Allows you to select:

• Table
• PivotTable Report
• PivotChart
• Only Create Connection
• Add this data to the Data Model

When to Use Connection Only

• For staging queries
• For queries used only in a merge
• For queries used only in an append
• When loading only the final combined result

45. Refreshing Power Query

Refresh One Query

1. Open Data → Queries & Connections.
2. Right-click the query.
3. Select Refresh.

Refresh Everything

Go to Data → Refresh All.

Refresh When Opening the Workbook

1. Open Queries & Connections.
2. Right-click the query and open Properties.
3. Select Refresh data when opening the file.

46. Source-File Path Problems

A query may fail when a source file or folder is moved, renamed or deleted.

Possible Solutions

• Restore the file to its original location.
• Update the source step.
• Use Data Source Settings to change the source.
• Use a parameter for a frequently changing folder path.
• Store shared files in a stable approved location.

47. Privacy Levels and Credentials

Power Query may request credentials and privacy settings when connecting to external sources.

Common Privacy Levels

• Private
• Organizational
• Public

Select privacy settings based on the sensitivity and ownership of the data. Do not disable privacy protections merely to bypass an error without understanding the risk.

48. Common Power Query Errors

Column Not Found

A source heading may have been renamed or removed. Review the source and the Applied Steps.

Data-Type Conversion Error

A column may contain text where a date or number is expected.

Formula.Firewall or Privacy Error

The query may be combining sources with incompatible privacy settings.

File Not Found

The source file or folder path has changed.

Folder Combination Error

One or more files may use a different structure, sheet name or column layout.

Duplicate-Key Merge Problem

The matching key in the master table may not be unique, causing transactions to expand into multiple rows after the merge.

49. Power Query Best Practices

• Keep source data unchanged.
• Use meaningful query names.
• Set correct data types early.
• Remove unnecessary rows and columns.
• Use a stable and consistent folder structure.
• Keep monthly file schemas consistent.
• Use unique keys in master tables.
• Investigate errors before removing them.
• Use staging queries and references.
• Disable loading for unnecessary intermediate queries.
• Rename important Applied Steps.
• Filter large datasets as early as practical.
• Test the query with new files before deployment.
• Document source locations and refresh instructions.

50. Practical Assignment: Automated Monthly Sales Consolidation

Create an automated report that combines monthly sales files stored in one folder.

Required Folder Structure

Advanced_Excel_Project
├── Source_Files
│   ├── Sales_January.xlsx
│   ├── Sales_February.xlsx
│   ├── Sales_March.xlsx
│   └── Sales_April.xlsx
├── Master_Data
│   ├── Product_Master.xlsx
│   └── Customer_Master.xlsx
└── Reports
    └── Automated_Sales_Report.xlsx

Required Monthly Sales Columns

• Order ID
• Order Date
• Customer ID
• Product ID
• Region
• Quantity
• Unit Price
• Status

Assignment Tasks

1. Connect to the Source_Files folder.
2. Keep only valid Excel files.
3. Exclude hidden and temporary files.
4. Combine all monthly files.
5. Promote the correct headers.
6. Set accurate data types.
7. Remove blank rows.
8. Remove duplicate Order IDs where appropriate.
9. Trim and clean text columns.
10. Standardize Region and Status values.
11. Merge the combined sales query with Product Master.
12. Merge it with Customer Master.
13. Calculate Total Sales.
14. Calculate Total Cost and Profit.
15. Add Year, Quarter and Month columns.
16. Create an exception query for unmatched Product IDs.
17. Load the final result into Excel or the Data Model.
18. Create a PivotTable and sales dashboard.
19. Add another monthly file and test Refresh All.

51. Practice Exercises

Exercise 1: Clean Customer Data

Import a CSV file, remove blank rows, clean names, standardize email addresses, split Location and remove duplicate Customer IDs.

Exercise 2: Append Regional Reports

Import East, West, North and South sales tables and append them into one national sales query.

Exercise 3: Merge Employee and Department Data

Merge Employee Transactions with Department Master using Department ID.

Exercise 4: Unpivot Monthly Sales

Convert January-to-December columns into Month and Sales rows.

Exercise 5: Create an Exception Report

Use a Left Anti merge to identify transaction Product IDs that do not exist in Product Master.

52. Chapter Quiz

1. What does ETL mean?
2. What is the purpose of Applied Steps?
3. Why are correct data types important?
4. What is the difference between Append and Merge?
5. Which join keeps every row from the first table?
6. Which join identifies unmatched rows from the first table?
7. What is the purpose of Unpivot Columns?
8. Why should files in a folder use consistent structures?
9. What is the difference between Reference and Duplicate?
10. What is a staging query?
11. When should a query be loaded as Connection Only?
12. What happens when Refresh is selected?

53. Frequently Asked Questions

Does Power Query change the original source data?

No. Power Query reads the source and applies transformations to the query result. It does not normally rewrite the original file.

What is the difference between Power Query and Excel formulas?

Power Query is primarily used to import, clean, reshape and combine datasets before analysis. Excel formulas calculate values within worksheets after data is loaded.

What is the difference between Append and Merge?

Append adds rows from one table below another. Merge adds related columns by matching keys between tables.

Can Power Query combine new monthly files automatically?

Yes. When files follow a consistent schema and are placed in the connected folder, Refresh can include them in the combined result.

Why did a merge increase the number of rows?

The matching column in the second table probably contains duplicate values. One source row can match several records and expand after the merge.

Do Power Query reports update automatically?

Queries update when refreshed. You can refresh manually, use Refresh All or configure selected queries to refresh when the workbook opens.

Official References

• About Power Query in Excel – Microsoft Support
• Import Data from Data Sources – Microsoft Support
• Append Queries – Microsoft Learn
• Merge Queries Overview – Microsoft Learn
• Import Data from a Folder – Microsoft Support

Conclusion

Power Query transforms repetitive data-cleaning work into a repeatable and refreshable process. It can connect to Excel, CSV, folders, websites and databases before cleaning and reshaping the imported data.

Append combines rows from multiple tables, while Merge adds related information through matching keys. The Folder connector can consolidate monthly files automatically, and Refresh repeats the complete transformation whenever new data becomes available.

In the next chapter, we will study Power Pivot and Data Modelling, including fact tables, dimension tables, star schemas, relationships, calendar tables and the Excel Data Model.