Power Query Trong Excel: Nhập, Biến Đổi Và Gộp Dữ Liệu Tự Động

1. Power Query là gì? #

Power Query (hay Get & Transform) là công cụ ETL (Extract, Transform, Load) tích hợp sẵn trong Excel 2016+ và 365. Nó giúp bạn nhập dữ liệu từ nhiều nguồn, biến đổi theo ý muốn, và nạp vào bảng tính — tất cả chỉ cần setup MỘT LẦN, sau đó Refresh để cập nhật tự động.

1.1. Mở Power Query #

• Excel 2016/365: Data → Get Data hoặc Data → From Table/Range

• Excel 2013: Cài Add-in Power Query riêng

• Excel 2010 trở xuống: Không hỗ trợ

2. Import dữ liệu — Nguồn vào #

2.1. Từ file Excel/CSV #

Data → Get Data → From File → From Workbook/CSV

Power Query tự nhận dạng:

• Delimiter (dấu phẩy, tab, semicolon)

• Header row

• Data type (text, number, date)

2.2. Từ thư mục (Combine Files) #

Import TẤT CẢ file Excel/CSV trong 1 thư mục:

1. Data → Get Data → From File → From Folder

2. Chọn thư mục → Combine → Combine & Transform Data

Cực mạnh khi bạn nhận báo cáo hàng tháng — mỗi tháng 1 file → Power Query gộp tất cả tự động.

2.3. Từ Web #

Data → Get Data → From Other Sources → From Web

Nhập URL → Power Query parse HTML table trên website → chọn bảng muốn import.

2.4. Từ Database #

Hỗ trợ: SQL Server, MySQL, PostgreSQL, Oracle, Access...

3. Editor — Giao diện biến đổi #

Khi import xong, Power Query Editor mở ra với:

3.1. Applied Steps — Ghi nhớ thao tác #

Mỗi thao tác biến đổi = 1 step. Bạn có thể:

• Click vào step cũ → xem dữ liệu tại thời điểm đó

• Xóa step → undo thao tác

• Thêm step ở giữa → chèn biến đổi

4. Transform — Các thao tác biến đổi phổ biến #

4.1. Đổi Data Type #

Click icon ở đầu cột → chọn: Text, Number, Date, Decimal...

4.2. Xóa cột không cần #

Chọn cột → Remove Columns (hoặc giữ Ctrl chọn cột cần giữ → Remove Other Columns)

4.3. Filter dữ liệu #

Click dropdown đầu cột → bỏ tick giá trị không cần (giống Auto Filter)

4.4. Split Column #

Transform → Split Column → By Delimiter

Ví dụ: Cột "Họ và tên" → split bằng dấu cách → tách thành Họ, Đệm, Tên

4.5. Replace Values #

Transform → Replace Values

Thay thế: "KD" → "Kinh doanh", "" (trống) → "N/A"

4.6. Pivot và Unpivot #

• Unpivot: Chuyển cột thành hàng (flatten dữ liệu cho Pivot Table)

• Pivot: Chuyển hàng thành cột

Ví dụ Unpivot: Bảng có cột Tháng 1, Tháng 2... → Unpivot thành 2 cột: Tháng | Giá trị

4.7. Add Column — Tạo cột mới #

• Custom Column: Viết công thức M

• Column from Examples: Gõ ví dụ → Power Query tự đoán pattern

• Conditional Column: If-then-else tạo cột phân loại

5. Merge Queries — Nối bảng ngang (JOIN) #

Tương tự VLOOKUP nhưng mạnh hơn:

1. Home → Merge Queries

2. Chọn bảng phụ

3. Chọn cột khóa chung

4. Chọn Join Kind:

1. Expand cột kết quả → chọn cột muốn lấy

6. Append Queries — Nối bảng dọc (UNION) #

Gộp dữ liệu cùng cấu trúc từ nhiều bảng:

1. Home → Append Queries

2. Chọn bảng cần gộp (2 hoặc 3+)

3. OK → dữ liệu xếp chồng theo chiều dọc

Hữu ích: gộp báo cáo tháng 1, tháng 2... thành bảng tổng.

7. Load — Nạp vào Excel #

Home → Close & Load

8. Refresh — Cập nhật tự động #

• Data → Refresh All hoặc Ctrl+Alt+F5

• Click phải Table → Refresh

• Tự động: Connection Properties → Refresh every X minutes

Khi refresh, Power Query chạy lại toàn bộ steps → dữ liệu mới nhất.

9. Mẹo Power Query #

1. Đặt tên step rõ ràng: Click phải step → Rename → dễ debug

2. Reference query: Tạo query tham chiếu query khác → tái sử dụng

3. Dùng Column from Examples: Không cần viết M → gõ ví dụ, Power Query tự đoán

4. Group By: Tính SUM, COUNT, AVG theo nhóm → thay thế Pivot Table

5. Connection Only: Load dữ liệu lớn vào connection, chỉ load kết quả cuối vào sheet

10. Tổng kết #

Power Query là công cụ mạnh nhất trong Excel mà nhiều người chưa biết đến. Nếu bạn dành hơn 30 phút mỗi tuần để copy-paste dữ liệu giữa các file, Power Query sẽ giảm thời gian đó xuống còn 1 cú click Refresh.