Ngày 1: Power Query Editor, làm sạch dữ liệu cơ bản

Kế hoạch 4 giờ

Thời gian	Nội dung
Giờ 1	Phần 1 - 3: Power Query là gì, giao diện, khám phá file bẩn
Giờ 2 - 3	Phần 4: làm sạch từng bước
Giờ 4	Bài tập, trong đó có 2 “cái bẫy” kinh điển

1. Bối cảnh

Phòng kinh doanh gửi bạn file DonHang_Q1_2025_XuatTuPhanMem.xlsx, xuất từ phần mềm bán hàng của công ty. Mở bằng Excel, bạn sẽ thấy:

• 3 dòng tiêu đề công ty, ngày xuất, 1 dòng trống, rồi mới đến dòng tên cột.
• Cứ khoảng 400 dòng, phần mềm lại chèn 1 dòng trống và lặp lại dòng tên cột (do phân trang khi in).
• Cuối file có dòng Tổng cộng.
• Ngày đặt là văn bản kiểu 07/01/2025. Đơn giá là văn bản kiểu 168.000. CK là 5% hoặc để trống.
• Tên cửa hàng có khoảng trắng thừa. Kênh viết lộn xộn (GT, gt, GT, Mt…).
• Một số dòng bị trùng do phần mềm xuất lỗi.

Nếu sửa tay trong Excel, tháng sau bạn lại phải sửa tay từ đầu. Với Power Query, bạn làm một lần. Tháng sau chỉ cần thay file và bấm Refresh.

2. Power Query là gì?

Power Query là “dây chuyền xử lý dữ liệu” có sẵn trong Power BI (và cả trong Excel, ở tab Data > Get Data).

• Mỗi thao tác bạn làm (xóa dòng, đổi kiểu, tách cột…) được ghi lại thành một bước (Applied Step).
• Mỗi lần Refresh, Power Query chạy lại toàn bộ các bước theo thứ tự trên dữ liệu mới nhất.
• File gốc không bao giờ bị sửa.
• Đằng sau mỗi bước là một dòng code bằng ngôn ngữ M. Bạn chưa cần viết M, nhưng sẽ học đọc dần từ hôm nay (học kỹ ở tuần 4).

3. Giao diện Power Query Editor

Số	Vùng	Dùng để
1	Ribbon	Các tab Home, Transform, Add Column, View
2	Queries	Danh sách các query (mỗi query thường cho ra 1 bảng)
3	Formula bar	Code M của bước đang chọn. Nếu không thấy: View > bật Formula Bar
4	Column quality	Tỷ lệ Valid / Error / Empty của từng cột
5	Xem trước	Dữ liệu tại bước đang chọn. Chỉ là bản xem trước, tối đa khoảng 1.000 dòng đầu
6	Applied Steps	Danh sách các bước. Bấm vào một bước để xem dữ liệu tại thời điểm đó
7	Thanh trạng thái	Số cột, số dòng; phạm vi profiling

Hai điểm khác biệt quan trọng giữa Transform và Add Column:

• Tab Transform: biến đổi ngay trên cột đang có.
• Tab Add Column: tạo cột mới, giữ nguyên cột cũ.

4. Thực hành: làm sạch từng bước

4.1 Mở file trong Power Query

1. Tạo file Power BI mới, lưu thành SaoMai_PQ_Tuan2.pbix.
2. Home > Get data > Excel workbook > chọn DonHang_Q1_2025_XuatTuPhanMem.xlsx.
3. Trong Navigator, tick Sheet1 > bấm Transform Data (không bấm Load).
4. Cửa sổ Power Query Editor mở ra. Ở pane Query Settings, đổi Name thành DonHang.

4.2 Bật công cụ soi dữ liệu

View > tick Column quality, Column distribution, Column profile.

Ở thanh trạng thái phía dưới, bấm vào chữ Column profiling based on top 1000 rows, chọn Column profiling based on entire data set để thống kê toàn bộ dữ liệu thay vì 1.000 dòng đầu.

4.3 Xem Power Query đã tự làm gì

Nhìn Applied Steps. Có thể Power Query đã tự thêm vài bước: Source, Navigation, có khi thêm Promoted Headers và Changed Type.

• Bấm vào bước Promoted Headers (nếu có). Tên cột đang là “CÔNG TY CP HÀNG TIÊU DÙNG SAO MAI”, “Column2”… Power Query đã lấy nhầm dòng tiêu đề công ty làm tên cột.
• Xóa 2 bước tự động này: bấm dấu X bên trái tên bước Changed Type, rồi đến Promoted Headers. Xóa từ dưới lên.

Giờ dữ liệu có các cột Column1 … Column12, dòng 1 là “CÔNG TY CP…”.

4.4 Bỏ dòng rác phía trên và lấy tên cột

1. Home > Remove Rows > Remove Top Rows > nhập 4 > OK. Đã bỏ 3 dòng tiêu đề và 1 dòng trống.
2. Home > Use First Row as Headers. Dòng “Mã đơn hàng, Ngày đặt…” trở thành tên cột.
3. Power Query lại tự thêm bước Changed Type. Xóa bước này đi. Ta sẽ đổi kiểu ở cuối.

4.5 Bỏ dòng trống

Home > Remove Rows > Remove Blank Rows. Bước này xóa các dòng mà mọi ô đều trống.

4.6 Bỏ dòng lặp tên cột và dòng Tổng cộng

1. Bấm mũi tên lọc ở tiêu đề cột Mã đơn hàng.
2. Bỏ tick 2 giá trị Mã đơn hàng và Tổng cộng. Nếu danh sách quá dài, gõ vào ô tìm kiếm.
3. Nếu Power Query báo List may be incomplete, bấm Load more.

Có thể làm chắc chắn hơn: mũi tên lọc > Text Filters > Does Not Equal > Mã đơn hàng > And > Does Not Equal > Tổng cộng.

Xem code M của bước vừa tạo trên Formula bar:

= Table.SelectRows(#"Removed Blank Rows", each ([Mã đơn hàng] <> "Mã đơn hàng" and [Mã đơn hàng] <> "Tổng cộng"))

Đọc như câu tiếng Việt: “Từ bảng ở bước Removed Blank Rows, chọn các dòng mà mỗi dòng có Mã đơn hàng khác ‘Mã đơn hàng’ và khác ‘Tổng cộng’.”

4.7 Bỏ cột không cần

Chuột phải tiêu đề cột Ghi chú > Remove.

4.8 Cắt khoảng trắng thừa

1. Chọn các cột văn bản: Tên cửa hàng, Kênh, Vùng, Tên sản phẩm (Ctrl + click).
2. Transform > Format > Trim. Bỏ khoảng trắng ở đầu và cuối chuỗi.
3. Tiếp tục Transform > Format > Clean. Bỏ các ký tự điều khiển ẩn, ví dụ ký tự xuống dòng.

4.9 Bỏ dòng trùng

1. Bấm vào ô góc trên trái của bảng (biểu tượng bảng nhỏ) > Remove Duplicates.
   • Hoặc chọn tất cả các cột (Ctrl + A khi đang chọn một tiêu đề cột) > Home > Remove Rows > Remove Duplicates.
2. Bước này giữ lại một dòng cho mỗi nhóm dòng giống hệt nhau trên mọi cột đang chọn.

4.10 Đổi kiểu dữ liệu, kể cả định dạng Việt Nam

Bấm biểu tượng kiểu dữ liệu ở bên trái tên cột (ABC, 123, lịch…) để đổi kiểu.

Cột	Giá trị mẫu	Kiểu đích	Cách đổi
Mã đơn hàng, Mã CH, Tên cửa hàng, Kênh, Vùng, Mã SKU, Tên sản phẩm	Văn bản	Text	Bình thường
SL (thùng)	2 (vài dòng là văn bản "2")	Whole Number	Bình thường
CK	5%	Percentage	Bình thường
Ngày đặt	07/01/2025 (ngày 7 tháng 1)	Date	Using Locale
Đơn giá	168.000 (một trăm sáu mươi tám nghìn)	Whole Number	Using Locale

Đổi kiểu bằng Locale:

1. Chuột phải cột Ngày đặt > Change Type > Using Locale…
2. Data Type = Date, Locale = Vietnamese (Vietnam) > OK.
3. Làm tương tự với Đơn giá: Data Type = Whole Number, Locale = Vietnamese (Vietnam).

Ảnh chụp màn hình sẽ được bổ sung

Hộp thoại “Change Type with Locale”: Data Type = Date, Locale = Vietnamese (Vietnam), phần xem trước bên dưới.

Vì sao cần Locale?

• Theo kiểu Mỹ (en-US), 07/01/2025 là ngày 1 tháng 7, và 168.000 là 168 phẩy 000, tức 168.
• Theo kiểu Việt Nam (vi-VN), 07/01/2025 là ngày 7 tháng 1, và 168.000 là một trăm sáu mươi tám nghìn.

Power Query mặc định dùng cài đặt vùng của file .pbix, xem ở File > Options and settings > Options > Current File > Regional Settings. Máy bạn đặt kiểu gì thì Power Query hiểu kiểu đó. Using Locale giúp kết quả đúng trên mọi máy.

4.11 Kiểm tra và nạp

1. Nhìn Column quality: mọi cột phải Valid 100%, Error 0%. CK có thể Empty khoảng 53%, vì nhiều dòng không có chiết khấu.
2. Thanh trạng thái: 12 → 11 COLUMNS, số dòng: xem bài tập 1.
3. Home > Close & Apply. Dữ liệu được nạp vào Power BI.

4.12 Đọc lại toàn bộ code

Home > Advanced Editor. Bạn sẽ thấy toàn bộ các bước dưới dạng code M, đại ý như sau (đường dẫn file và tên bước có thể khác):

let
    Source = Excel.Workbook(File.Contents("C:\KhoaHoc\data\tuan-02\DonHang_Q1_2025_XuatTuPhanMem.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(Sheet1_Sheet, 4),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Removed Blank Rows" = Table.SelectRows(#"Promoted Headers", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Filtered Rows" = Table.SelectRows(#"Removed Blank Rows", each ([Mã đơn hàng] <> "Mã đơn hàng" and [Mã đơn hàng] <> "Tổng cộng")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows", {"Ghi chú"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns", {{"Tên cửa hàng", Text.Trim, type text}, {"Kênh", Text.Trim, type text}, {"Vùng", Text.Trim, type text}, {"Tên sản phẩm", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text", {{"Tên cửa hàng", Text.Clean, type text}, {"Kênh", Text.Clean, type text}, {"Vùng", Text.Clean, type text}, {"Tên sản phẩm", Text.Clean, type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Cleaned Text"),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Duplicates", {{"Ngày đặt", type date}, {"Đơn giá", Int64.Type}}, "vi-VN"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"Mã đơn hàng", type text}, {"Mã CH", type text}, {"Mã SKU", type text}, {"SL (thùng)", Int64.Type}, {"CK", Percentage.Type}})
in
    #"Changed Type"

Chưa cần hiểu hết. Chỉ cần nhận ra 3 điều:

1. Mỗi dòng giữa let và in là một bước trong Applied Steps.
2. Mỗi bước dùng kết quả của bước ngay trước nó, ví dụ #"Removed Columns" dùng #"Filtered Rows".
3. Dòng sau in là kết quả cuối cùng.

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

Kiểm tra kết quả làm sạch. Sau khi làm xong phần 4:

1. Bảng DonHang có bao nhiêu dòng? Xem ở Table view, hoặc tạo card Count of Mã đơn hàng.
2. Tổng SL (thùng) là bao nhiêu?
3. Dòng Tổng cộng trong file gốc ghi tổng SL là bao nhiêu? Vì sao khác với con số của bạn?

Cái bẫy số 1: thứ tự bước. Trong Applied Steps, kéo bước Removed Duplicates lên trước bước Trimmed Text (kéo thả để đổi thứ tự).

1. Số dòng cuối cùng bây giờ là bao nhiêu?
2. Vì sao?
3. Kéo về đúng thứ tự cũ.

Cái bẫy số 2: Locale. Tạo một bản sao query: chuột phải DonHang trong pane Queries > Duplicate, đặt tên DonHang_ThuLocale. Trong bản sao:

1. Xóa bước Changed Type with Locale.
2. Đổi kiểu Ngày đặt sang Date và Đơn giá sang Whole Number bằng Using Locale, nhưng chọn Locale = English (United States).
3. Quan sát: dòng có Ngày đặt gốc 07/01/2025 thành ngày nào? Dòng có ngày 13/01/2025 thì sao? Đơn giá 168.000 thành bao nhiêu?
4. Xóa query thử nghiệm này.

Đọc Column profile. Với bảng DonHang đã sạch (profiling trên entire data set):

1. Cột CK có bao nhiêu % Empty? Ý nghĩa kinh doanh của ô trống ở cột này là gì?
2. Cột Kênh có bao nhiêu giá trị khác nhau (distinct)? Vì sao không phải 2?
3. Cột Vùng có bao nhiêu giá trị khác nhau?

Mô phỏng tháng sau. Đây là phép thử quan trọng nhất của Power Query.

1. Mở file Excel gốc, xóa khoảng 2.000 dòng dữ liệu bất kỳ ở giữa, lưu thành tên khác (để giữ bản gốc), ví dụ DonHang_Thang_Sau.xlsx.
2. Trong Power Query, chọn bước Source > bấm biểu tượng bánh răng ⚙ > đổi đường dẫn sang file mới > OK.
3. Bấm Home > Refresh Preview. Mọi bước có chạy không lỗi không? Số dòng thay đổi ra sao?
4. Đổi lại về file gốc.

6. Tự đánh giá