Bắt đầu học

Làm sạch dữ liệu: lập hồ sơ (profiling) và các phép biến đổi cơ bản

Xem thử

Phòng chăm sóc khách hàng của GDev Mart xuất một tệp customers_export.csv gồm 60.000 dòng. Bạn nạp thẳng vào Power BI, dựng biểu đồ "số khách theo thành phố" và thấy bảy thành phố tên gần giống nhau: Ha Noi, Hà Nội, HA NOI, ha noi , Hanoi... Cột signup_date có 312 ô trống và vài ô ghi N/A; cột age có giá trị -1 và 999. Biểu đồ trông hợp lý nhưng mọi con số đều lệch.

Hãy dự đoán: trước khi vẽ bất kỳ biểu đồ nào, bạn muốn biết điều gì về từng cột của bảng, và bằng công cụ nào trong Power BI? Nêu ít nhất ba câu hỏi bạn sẽ đặt ra cho dữ liệu.

Sau bài này, bạn có thể

  • Lập hồ sơ cột bằng Column quality, Column distribution và Column profile, và đổi sang tính trên toàn bộ dữ liệu khi cần kết luận chính xác.
  • Chọn phép biến đổi phù hợp cho từng loại lỗi: Trim, Replace Values, Remove Duplicates, Change Type Using Locale.
  • Giải thích vì sao giá trị vô lý nên thành null thay vì 0, và vì sao phải thay N/A bằng null trước khi đổi kiểu ngày.

Cần biết trước: bài "Power BI trong nền tảng dữ liệu: nguồn gold, chế độ kết nối và kiến trúc"; một tệp CSV hoặc Excel bất kỳ để nạp thử vào Power Query Editor.

Mô hình tư duy 1: nhìn dữ liệu trước khi sửa dữ liệu

Trong Power Query Editor, tab View có ba công cụ lập hồ sơ cột (data profiling):

Công cụCho bạn thấyPhát hiện được
Column qualityPhần trăm giá trị hợp lệ (Valid), lỗi (Error), trống (Empty)Cột có nhiều ô trống hoặc lỗi ép kiểu
Column distributionSố giá trị khác nhau (Distinct) và duy nhất (Unique)Khóa không duy nhất, cột có quá ít hoặc quá nhiều giá trị
Column profileThống kê chi tiết và biểu đồ tần suất từng giá trịGiá trị bất thường (-1, 999), biến thể của cùng một nhãn

Một điều cần nhớ: theo mặc định, hồ sơ chỉ tính trên 1.000 dòng đầu. Với bảng 60.000 dòng, bạn có thể bỏ sót lỗi nằm ở dòng 40.000. Hãy đổi sang "Column profiling based on entire data set" ở thanh trạng thái phía dưới khi cần kết luận chính xác.

Quy trình làm sạch có kỷ luật gồm bốn việc: (1) lập hồ sơ để biết vấn đề, (2) quyết định cách xử lý từng vấn đề (sửa, loại bỏ, hay báo lại nguồn), (3) thực hiện bằng các bước có tên rõ ràng, (4) kiểm tra lại bằng hồ sơ sau khi sửa. Bỏ bước 2 là lỗi phổ biến: nhiều người xóa dòng lỗi mà không hỏi vì sao chúng xuất hiện.

Đang tải sơ đồ…

Cách đọc hình: vòng lặp bốn bước. Sau khi sửa, hồ sơ lại để xác nhận; nếu lộ ra vấn đề mới (thường là vậy) thì quay lại bước quyết định. Một truy vấn sạch thường đi qua hai hoặc ba vòng như thế.

Mô hình tư duy 2: mỗi loại lỗi có một cách xử lý riêng

Vấn đề thường gặpPhép biến đổi (Transform / Add Column)Ví dụ ở GDev Mart
Khoảng trắng thừaFormat > Trim, Clean (bỏ ký tự điều khiển)"ha noi " thành "ha noi"
Hoa thường không nhất quánFormat > Capitalize Each Word / UPPERCASEHA NOI, ha noi thành một dạng
Nhiều cách viết một nhãnReplace Values hoặc bảng ánh xạ rồi MergeHanoi, Ha Noi thành Hà Nội
Ô trống nên điềnFill Down / Up, Replace Values (null thành giá trị)Mã vùng chỉ ghi ở dòng đầu mỗi nhóm
Giá trị N/A là chữReplace Values N/A thành null rồi đổi kiểu ngàyCột signup_date
Giá trị vô lýFilter rows hoặc Replace Values thành nullage = -1 hoặc 999
Bản ghi trùngRemove Duplicates trên cột khóaHai dòng cùng customer_id
Một cột chứa hai thông tinSplit Column theo dấu phân cách"Nguyễn Văn A - 0912..."
Lỗi ép kiểuRemove Errors hoặc Replace Errors, nhưng phải tìm nguyên nhân12,5 bị đọc sai do dấu phẩy

Ba nguyên tắc ứng xử với lỗi:

  1. Không xóa im lặng. Nếu bỏ 312 dòng thiếu ngày, hãy ghi lại và báo cho chủ sở hữu dữ liệu; 312 dòng có thể là một lỗi hệ thống ở nguồn.
  2. Giá trị vô lý thành null, không thành 0. age = 0 là một số liệu sai trông có vẻ hợp lệ và kéo tuổi trung bình xuống; null bị bỏ qua khỏi trung bình, đúng như bản chất "không biết".
  3. Sửa ở nguồn khi có thể. Nếu cùng lỗi lặp lại hằng tuần, đưa quy tắc vào dbt (silver) thay vì để mỗi báo cáo tự sửa, như đã bàn ở chương 7 và chương 13.

Kiểu dữ liệu và vùng ngôn ngữ (locale)

Cùng một chuỗi 03/04/2026 là 3 tháng 4 hay 4 tháng 3 tùy vùng ngôn ngữ; 1.234,5 là một nghìn hai trăm... hay lỗi? Power BI đoán theo cấu hình máy, nên máy hai người cùng mở một tệp có thể ra hai kết quả. Cách chắc chắn: chuột phải cột, chọn Change Type > Using Locale và chỉ định rõ kiểu cùng vùng (ví dụ English (United States) cho dữ liệu MM/DD/YYYY, hoặc Vietnamese cho DD/MM/YYYY). Với dữ liệu số, các phép biến đổi trên tab Transform (nhóm Number Column) như Round, Standard (cộng, nhân, chia, phần trăm) và Statistics hữu ích khi bạn cần đổi đơn vị (từ đồng sang triệu đồng) hay làm tròn; nhớ đổi đơn vị bằng phép nhân chứ không gõ tay từng ô.

Đang tải sơ đồ…

Cách đọc hình: ba phép biến đổi nối tiếp cho một cột ngày bẩn. Thứ tự quan trọng: phải thay N/A thành null trước khi đổi kiểu, nếu không phép đổi kiểu tạo ra lỗi ở những ô chữ.

Đang tải Animation…

Ví dụ mã M ghi lại từ các bước trên

text
let
    Source = Csv.Document(File.Contents("C:\data\customers_export.csv"), [Delimiter = ",", Encoding = 65001]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    Trimmed = Table.TransformColumns(Promoted, {{"city", each Text.Proper(Text.Trim(_)), type text}}),
    CityFixed = Table.ReplaceValue(Trimmed, each [city], each if [city] = "Ha Noi" or [city] = "Hanoi" then "Hà Nội" else [city], Replacer.ReplaceValue, {"city"}),
    NaToNull = Table.ReplaceValue(CityFixed, "N/A", null, Replacer.ReplaceValue, {"signup_date"}),
    Typed = Table.TransformColumnTypes(NaToNull, {{"signup_date", type date}, {"age", Int64.Type}}, "en-US"),
    AgeFixed = Table.ReplaceValue(Typed, each [age], each if [age] <> null and ([age] < 0 or [age] > 120) then null else [age], Replacer.ReplaceValue, {"age"}),
    Deduped = Table.Distinct(AgeFixed, {"customer_id"})
in
    Deduped

Giải từng bước: Trimmed cắt khoảng trắng và viết hoa chữ đầu mỗi từ (Text.Proper) để ha noi và HA NOI về cùng một dạng; CityFixed ghép các biến thể còn lại; NaToNull đổi chữ N/A thành null; Typed đặt kiểu ngày với locale en-US nên MM/DD/YYYY đọc đúng; AgeFixed biến tuổi ngoài khoảng 0 đến 120 thành null; Deduped giữ một dòng cho mỗi customer_id.

Một lưu ý nhỏ: Table.Distinct giữ dòng đầu tiên của mỗi khóa; nếu cần giữ bản ghi mới nhất, sắp xếp giảm dần theo ngày cập nhật trước rồi mới khử trùng, và dùng Table.Buffer để thứ tự không bị thay đổi.

Những hiểu lầm thường gặp

  • "Sửa dữ liệu trong Power Query là sửa tệp gốc." Mỗi bước chỉ là công thức chạy lại khi làm mới; tệp nguồn không đổi.
  • "Lỗi thì xóa dòng cho nhanh." Xóa im lặng che giấu một lỗi ở nguồn; hãy ghi lại số dòng bị loại và báo chủ sở hữu.
  • "Giá trị vô lý thay bằng 0 cho gọn." Số 0 kéo trung bình xuống và trông như dữ liệu thật; null mới đúng nghĩa "không biết".
  • "Hồ sơ cột nhìn qua là đủ." Mặc định chỉ tính 1.000 dòng đầu; lỗi ở dòng sau có thể bị bỏ sót.
  • "Kiểu dữ liệu tự nhận trên máy tôi cũng đúng trên máy khác." Locale khác nhau cho kết quả khác; hãy chỉ định rõ.

Tự kiểm tra (có đáp án)

Câu 1. Cột age có -1 và 999. Bạn thay bằng gì và vì sao? Đáp án: thay bằng null. Giá trị này là "không biết"; null bị bỏ qua khỏi phép trung bình, còn 0 hay giá trị vô lý làm lệch kết quả mà không bị phát hiện.

Câu 2. Vì sao phải thay N/A bằng null trước khi đổi kiểu ngày? Đáp án: vì chuỗi N/A không phải ngày, đổi kiểu trước sẽ sinh lỗi ở các ô đó; sau khi đã là null, phép đổi kiểu để nguyên ô trống.

Câu 3. Hồ sơ cột báo cột customer_id có 60.000 dòng nhưng chỉ 58.450 giá trị khác nhau. Điều này nói lên điều gì và làm gì tiếp? Đáp án: có 1.550 dòng trùng khóa. Cần tìm nguyên nhân (xuất trùng, nhiều phiên bản) rồi khử trùng đúng quy tắc (giữ bản mới nhất), và báo lại nguồn; nếu không, khóa trùng ở dimension sẽ làm sai tổng ở fact như sẽ thấy ở bài mô hình sao.

Nếu chỉ nhớ 3 điều

  1. Lập hồ sơ trước, sửa sau: Column quality, distribution, profile; nhớ đổi sang toàn bộ dữ liệu khi cần kết luận.
  2. Giá trị vô lý thành null, không xóa im lặng, và chỉ định locale khi đổi kiểu.
  3. Lỗi lặp lại thì đưa quy tắc về dbt; Power Query là nơi sửa nhanh cho nguồn ngoài hệ thống.

Nguồn tham khảo

Nội dung bài học