Lỗi
Bạn đã xây dựng truy vấn của mình, mọi thứ trông có vẻ ổn, và rồi—ĐÙNG. Một thanh thông báo lỗi màu vàng xuất hiện. Khi bạn thay đổi kiểu cột thành Date, Power Query trả về các ô chứa lỗi Error. Nhấp vào khoảng trắng bên cạnh lỗi sẽ tiết lộ nguyên nhân:
DataFormat.Error: We couldn't parse the input provided as a Date value.
Về cơ bản, Power Query đang nhìn vào một đoạn văn bản và không biết làm cách nào để chuyển nó thành một ngày trên lịch.
Tại sao lỗi này xảy ra?
Lỗi này thường xuất hiện khi công cụ xử lý gặp một giá trị không khớp với mẫu dự kiến. Các nguyên nhân phổ biến bao gồm:
- Không khớp định dạng vùng: Máy tính của bạn mong đợi định dạng
DD/MM/YYYY(Vương quốc Anh/Việt Nam), nhưng dữ liệu của bạn lại làMM/DD/YYYY(Hoa Kỳ). Ví dụ: 12/31/2023 sẽ gây lỗi trên hệ thống chuẩn Anh/Việt vì không có tháng 31. - Dữ liệu rác bị ẩn: Cột có các khoảng trắng thừa, ký tự không in được hoặc văn bản như "N/A" và "TBD".
- Định dạng số thô: Ngày tháng là một số nguyên thô như
20231225. Power Query chỉ thấy một số nguyên chứ không phải ngày Giáng sinh. - Ngày tháng không hợp lệ: Các mục nhập như
31/02/2023(ngày 31 tháng 2) hoặc00/00/0000.
Giải pháp 1: Sử dụng Locale (Cách làm chuyên nghiệp)
Đừng chỉ nhấp vào biểu tượng "Date" ở tiêu đề cột. Nếu dữ liệu của bạn đến từ một vùng khác, hãy sử dụng tính năng Using Locale. Điều này cho Power Query biết chính xác quy tắc khu vực nào cần tuân theo.
- Nhấp chuột phải vào tiêu đề cột.
- Chọn Change Type > Using Locale...
- Trong hộp thoại, đặt Data Type thành Date.
- Dưới mục Locale, chọn vùng mà dữ liệu gốc sử dụng (ví dụ: "English (United States)" cho các ngày định dạng kiểu Mỹ).
- Nhấn OK.
Thao tác này sẽ thêm mã văn hóa (culture code) vào mã M của bạn, giúp truy vấn ổn định ngay cả khi bạn chia sẻ nó với đồng nghiệp ở nước ngoài:
= Table.TransformColumnTypes(Source, {{"DateColumn", type date}}, "en-US")
Giải pháp 2: Làm sạch dữ liệu trước
Đôi khi dữ liệu trông có vẻ sạch sẽ nhưng lại chứa các ký tự vô hình. Hãy thực hiện ba bước này trước khi bạn cố gắng thay đổi kiểu dữ liệu:
- Trim: Nhấp chuột phải vào cột > Transform > Trim để loại bỏ các khoảng trắng ở đầu hoặc cuối.
- Clean: Nhấp chuột phải > Transform > Clean để loại bỏ các ký tự không in được.
- Replace: Sử dụng Replace Values để chuyển các chuỗi "N/A" hoặc "null" thành giá trị
nullthực sự.
Giải pháp 3: Xử lý số thô (YYYYMMDD)
Các hệ thống xuất dữ liệu thường trả về ngày tháng dưới dạng số có 8 chữ số như 20240512. Để khắc phục, bạn cần cắt chuỗi văn bản và ghép lại. Đầu tiên, hãy đảm bảo cột được đặt thành Text, sau đó thêm một Custom Column với công thức này:
let
DateStr = Text.From([DateColumn]),
Year = Text.Start(DateStr, 4),
Month = Text.Middle(DateStr, 4, 2),
Day = Text.End(DateStr, 2)
in
Date.FromText(Year & "-" & Month & "-" & Day)
Giải pháp 4: Sử dụng "Try...Otherwise" cho dữ liệu lỗi
Nếu bạn có một tập dữ liệu khổng lồ với một vài hàng bị lỗi không đáng để sửa thủ công, hãy sử dụng khối lệnh try. Điều này ngăn một vài ô lỗi làm hỏng toàn bộ quá trình làm mới dữ liệu. Trong một Cột tùy chỉnh (Custom Column), hãy sử dụng:
try Date.FromText([DateColumn]) otherwise null
Lệnh này sẽ thử chuyển đổi. Nếu thất bại, nó sẽ để trống ô đó (null) thay vì báo lỗi.
Cách kiểm tra sau khi sửa
Sau khi áp dụng các thay đổi, hãy kiểm tra lại kết quả. Chuyển đến tab View và chọn Column Quality. Bạn sẽ muốn thấy 100% Valid và 0% Error. Một cách kiểm tra nhanh khác? Thử sắp xếp cột. Nếu nó sắp xếp theo thứ tự thời gian, đó là kiểu Date. Nếu 10/01/2023 đứng trước 02/01/2023, nó vẫn đang bị xử lý như văn bản (Text).
Mẹo phòng ngừa
- Yêu cầu ISO 8601: Nếu bạn có thể kiểm soát nguồn dữ liệu (như SQL view), hãy yêu cầu định dạng
YYYY-MM-DD. Đây là tiêu chuẩn toàn cầu không bao giờ sai sót. - Khóa Locale: Luôn sử dụng phương pháp "Using Locale" nếu nhóm của bạn làm việc đa quốc gia. Nó giúp tránh lỗi "chạy tốt trên máy tôi" khi một người dùng ở Mỹ gửi tệp cho một người dùng ở Anh.
- Xác thực nguồn: Nếu dữ liệu đến từ một tệp Excel tải lên thủ công, hãy sử dụng Data Validation để ngăn người dùng nhập những ngày không tồn tại như "31 tháng 2" ngay từ đầu.