Power Query Nâng Cao: Unpivot, Merge, Append Và Custom Columns

1. Unpivot — Biến cột thành dòng #

1.1. Bài toán #

Dữ liệu dạng bảng chéo (crosstab):

Cần chuyển thành dạng danh sách (flat):

1.2. Cách Unpivot #

1. Data → Get Data → From Table/Range

2. Chọn các CỘT GIỐNG NHAU (T1, T2, T3, T4)

3. Transform → Unpivot Columns

4. Rename cột Attribute → "Tháng", Value → "Doanh số"

5. Close & Load

1.3. 3 loại Unpivot #

Dùng "Unpivot Other Columns" là an toàn nhất — khi thêm cột mới (T5, T6...), Power Query tự unpivot.

1.4. Mã M #

= Table.UnpivotOtherColumns(Source, {"Tên"}, "Tháng", "Doanh số")

2. Merge Queries — Join bảng #

2.1. Bài toán #

Bảng 1: Đơn hàng (order_id, product_id, qty)

Bảng 2: Sản phẩm (product_id, product_name, price)

Cần ghép tên sản phẩm + giá vào đơn hàng.

2.2. Cách Merge #

1. Data → Get Data → Combine Queries → Merge Queries

2. Chọn bảng 1 (Orders) → chọn cột product_id

3. Chọn bảng 2 (Products) → chọn cột product_id

4. Chọn loại Join

5. Expand cột kết quả → chọn product_name, price

2.3. 6 loại Join #

2.4. Left Anti Join #

Tìm sản phẩm CHƯA CÓ đơn hàng:

1. Merge Products với Orders

2. Chọn Left Anti Join

3. Kết quả: sản phẩm không tìm thấy trong bảng đơn hàng

2.5. Merge nhiều cột #

Click cột 1, giữ Ctrl + click cột 2 → merge trên CẢ 2 cột (composite key).

3. Append Queries — Gộp bảng #

3.1. Bài toán #

12 bảng doanh số tháng (cùng cấu trúc) → gộp thành 1 bảng.

3.2. Cách Append #

1. Data → Get Data → Combine Queries → Append Queries

2. Chọn "Three or more tables"

3. Chọn tất cả bảng cần gộp → OK

3.3. Append vs Merge #

3.4. Append từ thư mục #

Gộp TẤT CẢ file Excel trong 1 folder:

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

2. Chọn folder chứa các file

3. Combine & Load

Khi thêm file mới vào folder → Refresh → tự gộp!

4. Custom Column — Tạo cột tính toán #

4.1. Thêm Custom Column #

1. Add Column → Custom Column

2. Viết công thức M

4.2. Ví dụ: Tính doanh thu #

= [Qty] * [Price]

4.3. Ví dụ: Phân loại #

= if [Amount] > 1000000 then "VIP" else if [Amount] > 500000 then "Regular" else "Small"

4.4. Ví dụ: Nối text #

= [FirstName] & " " & [LastName]

4.5. Ví dụ: Trích xuất năm #

= Date.Year([OrderDate])

5. Conditional Column — Nhanh hơn Custom #

1. Add Column → Conditional Column

2. Giao diện visual: IF → THEN → ELSE

3. Không cần viết code M

Phù hợp cho phân loại đơn giản.

6. Group By — Tổng hợp #

6.1. Cách Group #

1. Transform → Group By

2. Chọn cột group (ví dụ: Category)

3. Chọn phép tính: Sum, Count, Average, Min, Max

6.2. Group nhiều mức #

Click Advanced → thêm nhiều cột group + nhiều aggregation:

• Group: Category, Region

• Aggregations: Sum of Amount, Count of Orders, Average of Qty

7. Pivot Column — Ngược Unpivot #

Biến giá trị dòng thành tên cột:

→ Pivot "Tháng" column, Values from "Doanh số":

8. Fill Down / Fill Up #

Xử lý merged cells sau khi import:

Chọn cột Nhóm → Transform → Fill → Down:

9. Mẹo Power Query nâng cao #

1. Unpivot Other Columns: Linh hoạt hơn Unpivot Columns

2. Left Anti Join: Tìm missing data nhanh

3. Append từ folder: Tự động hóa gộp nhiều file

4. Group By Advanced: Nhiều metric cùng lúc

5. Fill Down: Giải quyết merged cells sau import

6. Applied Steps: Click phải → Rename step → dễ đọc lại

10. Tổng kết #

Power Query nâng cao mở ra khả năng xử lý dữ liệu KHÔNG GIỚI HẠN: Unpivot chuyển đổi cấu trúc, Merge join bảng như SQL, Append gộp nhiều nguồn, Custom Column tính toán linh hoạt. Tất cả đều REPEATABLE — Refresh 1 click khi dữ liệu thay đổi.