Ngày 2: Power Query, biến đổi cột

Tiếp tục với file SaoMai_PQ_Tuan2.pbix và query DonHang (10.543 dòng) của hôm qua. Home > Transform data để mở lại Power Query.

Kế hoạch 4 giờ

1. Transform hay Add Column?

Nhiều lệnh xuất hiện ở cả hai tab (Format, Extract, Date…). Chọn tab nào tùy bạn muốn thay hay thêm.

2. Chuẩn hóa văn bản

2.1 Chữ hoa, chữ thường

1. Chọn cột Kênh > Transform > Format > UPPERCASE. Giờ chỉ còn GT và MT.
2. Chọn cột Vùng > Transform > Format > Capitalize Each Word. MIỀN BẮC, miền bắc đều thành Miền Bắc.

2.2 Khoảng trắng thừa ở giữa chuỗi

Trim chỉ cắt khoảng trắng ở đầu và cuối. “Tạp hóa Cô Lan” (2 dấu cách ở giữa) vẫn còn nguyên.

1. Chọn cột Tên cửa hàng > Transform > Replace Values.
2. Value To Find: gõ 2 dấu cách. Replace With: gõ 1 dấu cách.
3. OK.

3. Tách, trích, ghép

3.1 Trích một phần văn bản (Extract)

Mã SKU có dạng SM-NGK-001: tiền tố công ty, mã ngành, số thứ tự. Lấy mã ngành ra cột riêng:

1. Chọn cột Mã SKU > Add Column > Extract > Text Between Delimiters.
2. Start delimiter = -, End delimiter = - > OK.
3. Đổi tên cột mới (double-click tiêu đề) thành Mã ngành.

Các lựa chọn khác trong Extract:

3.2 Tách cột (Split Column)

Split Column biến một cột thành nhiều cột. Khác với Extract là lấy một phần sang cột mới.

Thử trên một bản sao: chọn Mã SKU > chuột phải > Duplicate Column. Chọn cột bản sao > Transform > Split Column > By Delimiter > - > Each occurrence of the delimiter > OK. Kết quả là 3 cột: SM, NGK, 001.

Xóa 3 cột vừa tách. Ta đã có Mã ngành ở 3.1.

3.3 Ghép cột (Merge Columns)

1. Chọn Mã CH, rồi Ctrl + click Tên cửa hàng. Thứ tự click chính là thứ tự ghép.
2. Add Column > Merge Columns > Separator = Custom - > New column name = Cửa hàng > OK.
3. Kết quả: CH0003 - Tạp hóa Bà Khánh.

4. Thay giá trị và xử lý null

4.1 Thay null bằng 0

Cột CK đang trống ở các dòng không có chiết khấu. Trong Power Query, ô trống là giá trị null, và mọi phép tính với null đều ra null: 2 * 168000 * (1 - null) = null.

1. Chọn cột CK > Transform > Replace Values.
2. Value To Find = null (gõ đúng chữ null), Replace With = 0 > OK.

4.2 Fill Down / Fill Up

Dùng khi một giá trị chỉ được ghi ở dòng đầu nhóm, các dòng dưới để trống với ý “giống dòng trên”. Đây là kiểu rất hay gặp trong báo cáo xuất từ phần mềm kế toán.

Thử với bảng nhỏ: Home > Enter Data, nhập bảng sau, đặt tên ThuFillDown > OK.

Chọn cột Nhà phân phối > Transform > Fill > Down. Các ô trống được điền bằng giá trị phía trên gần nhất.

Xóa query ThuFillDown sau khi thử xong (chuột phải > Delete).

5. Tạo cột mới

5.1 Từ cột ngày

Chọn Ngày đặt > Add Column > Date:

Hôm nay tạo 2 cột: Tháng (số) và Thứ (Name of Day).

5.2 Conditional Column: cột theo điều kiện

Đặt tên ngành đầy đủ từ Mã ngành:

1. Add Column > Conditional Column.
2. New column name = Ngành hàng.
3. Lần lượt thêm các mệnh đề (Add Clause):

4. Else = Khác > OK.

5.3 Custom Column: công thức tự viết

Tính Thành tiền = SL x Đơn giá x (1 - CK).

1. Add Column > Custom Column.
2. New column name = Thành tiền.
3. Custom column formula: bấm đúp tên cột ở danh sách bên phải để chèn, không cần gõ tay:

[#"SL (thùng)"] * [Đơn giá] * (1 - [CK])

4. OK. Đổi kiểu cột Thành tiền sang Fixed Decimal Number (ký hiệu $) hoặc Whole Number.

5.4 Column From Examples: cho máy tự đoán công thức

1. Chọn cột Ngày đặt > Add Column > Column From Examples > From Selection.
2. Ở cột mới bên phải, dòng đầu tiên (Ngày đặt 07/01/2025), gõ: T1/2025. Nhấn Enter.
3. Power Query tự đoán công thức và điền các dòng còn lại. Kiểm tra vài dòng tháng 2, tháng 3. Nếu đúng, đổi tên cột thành Kỳ > OK.
4. Xem Formula bar: Power Query đã tự viết code M cho bạn.

Column From Examples rất hữu ích khi bạn không biết hàm nhưng biết kết quả mong muốn. Luôn kiểm tra kỹ kết quả, vì máy có thể đoán sai quy luật.

6. Hoàn thiện và nạp

1. Sắp xếp lại các cột: kéo thả tiêu đề cột theo thứ tự hợp lý.
2. Kiểm tra Column quality: không có Error.
3. Close & Apply.

7. Bài tập tự làm

= Table.AddColumn(#"Previous Step", "Quy mô", each if [Thành tiền] < 1000000 then "Nhỏ" else if [Thành tiền] < 5000000 then "Vừa" else "Lớn")

#"Added Conditional Column1" = Table.AddColumn(..., "Quy mô", each if [Thành tiền] < 1000000 then "Nhỏ" else if [Thành tiền] < 3000000 then "Vừa" else "Lớn")

8. Tự đánh giá