Xử Lý Lỗi Định Dạng Ngày Tháng Trong Power Query

Khi bạn cố gắng chuyển đổi một cột dữ liệu sang định dạng Date trong Power Query, đôi khi bạn sẽ gặp phải thông báo Error. Lý do phổ biến nhất là Power Query không thể nhận dạng được định dạng ngày tháng gốc của dữ liệu, đặc biệt khi dữ liệu đó đến từ một quốc gia hoặc khu vực sử dụng quy ước định dạng ngày tháng khác.

Dưới đây là 4 phương pháp bạn có thể áp dụng để giải quyết vấn đề này:

1. Using Locale… trong Menu Kiểu dữ liệu

Đây là cách xử lý lỗi trực tiếp tại cột đang gặp vấn đề, phù hợp khi bạn chỉ cần sửa một vài cột hoặc các cột có nguồn gốc định dạng khác nhau.

Bước 1: Trong Power Query, chọn cột chứa dữ liệu ngày tháng bị lỗi.
Bước 2: Nhấp vào biểu tượng kiểu dữ liệu ở góc trên bên trái của tiêu đề cột (thường hiển thị là ABC, 123, hoặc biểu tượng cuốn lịch).
Bước 3: Trong menu thả xuống, chọn Using Locale...
Bước 4: Cửa sổ Change Type with Locale sẽ xuất hiện.
- Chọn Data Type là Date.
- Chọn Locale tương ứng với định dạng gốc của dữ liệu (ví dụ: English (United States) nếu dữ liệu gốc là MM/DD/YYYY, hoặc Vietnamese (Vietnam) nếu là DD/MM/YYYY).
Bước 5: Nhấn OK. Power Query sẽ chuyển đổi dữ liệu dựa trên Locale bạn đã chọn.

A screenshot of a computer

AI-generated content may be incorrect.

Lưu ý: Bạn cần lặp lại thao tác này cho mỗi cột ngày tháng bị lỗi nếu chúng có định dạng gốc khác nhau.

2. Thiết lập Locale trong Regional Settings (Cài đặt Khu vực) cho toàn bộ File

Nếu hầu hết dữ liệu trong file Excel của bạn đều đến từ cùng một khu vực có định dạng ngày tháng khác với cài đặt mặc định của máy tính, bạn có thể thay đổi cài đặt này cho toàn bộ file.

Bước 1: Từ cửa sổ Power Query Editor hoặc giao diện Excel chính, vào tab File.
Bước 2: Chọn Options and settings (Tùy chọn và cài đặt) > Options.
Bước 3: Trong cửa sổ Options, chọn Regional Settings ở menu Current File bên trái.
Bước 4: Trong phần Locale for import, chọn miền địa phương phù hợp với nguồn dữ liệu chính của bạn.
Bước 5: Nhấn OK.

Sau khi thiết lập, mọi thao tác chuyển đổi kiểu dữ liệu sang Date trong file này sẽ mặc định sử dụng Locale bạn đã chọn, bạn không cần phải dùng Using Locale... cho từng cột nữa (trừ khi cột đó có định dạng đặc biệt khác). Cài đặt này sẽ được lưu cùng với file, nên khi bạn chia sẻ file, người khác mở ra cũng sẽ áp dụng cài đặt này.

3. Thay đổi Regional Settings của Hệ điều hành

Phương pháp này sẽ thay đổi định dạng mặc định cho toàn bộ máy tính của bạn, ảnh hưởng đến tất cả ứng dụng, không chỉ Excel và Power Query. Cách này phù hợp nếu bạn thường xuyên làm việc với dữ liệu từ một khu vực cụ thể và muốn định dạng đó là mặc định trên máy.

Bước 1: Mở Settings (Cài đặt) của Windows.
Bước 2: Tìm đến mục Time & Language (Thời gian & Ngôn ngữ) > Language & region (Ngôn ngữ & Khu vực).
Bước 3: Trong phần Regional format (Định dạng khu vực), chọn khu vực/quốc gia có định dạng bạn muốn sử dụng làm mặc định.
Bước 4: Khởi động lại Excel (và có thể cả Power Query) để thay đổi có hiệu lực.

A screenshot of a phone

AI-generated content may be incorrect.

Cảnh báo: Việc thay đổi cài đặt này có thể ảnh hưởng đến cách hiển thị ngày giờ, số, tiền tệ trong các ứng dụng khác trên máy tính của bạn. Hãy cân nhắc kỹ trước khi áp dụng.

4. Dùng Công thức Tùy chỉnh với Column From Examples

Khi định dạng ngày tháng của bạn không theo chuẩn thông thường (ví dụ: 2024.Apr.10, 10-Tháng Tư-2024, 20241004) và các phương pháp trên không hiệu quả, bạn có thể thử tính năng Column From Examples của Power Query. Tính năng này cho phép Power Query tự động nhận diện quy luật chuyển đổi dựa trên một vài ví dụ bạn cung cấp.

Bước 1: Chọn cột có định dạng ngày tháng cần sửa.
Bước 2: Chuyển sang tab Add Column trong trình chỉnh sửa Power Query.
Bước 3: Nhấp vào nút Column From Examples. Chọn From Selection nếu bạn đã chọn cột ở bước 1.
Bước 4: Một cột mới trống sẽ xuất hiện bên phải. Bắt đầu nhập định dạng ngày tháng chuẩn bạn mong muốn vào các ô đầu tiên của cột mới này, tương ứng với dữ liệu ở cột gốc. Ví dụ, nếu cột gốc là 2024.04.10, bạn gõ 10/04/2024 vào ô đầu tiên của cột mới.
Bước 5: Sau khi bạn nhập một vài ví dụ (thường là 2-3), Power Query sẽ cố gắng đoán quy luật và tự động điền các giá trị còn lại trong cột mới theo định dạng bạn mong muốn. Hãy kiểm tra kỹ các giá trị được đề xuất.
Bước 6: Nếu các giá trị đề xuất đã chính xác, nhấn OK.
Bước 7: Power Query sẽ tạo ra một cột mới với dữ liệu ngày tháng đã được định dạng đúng. Bây giờ, hãy đổi Data Type của cột mới này thành Date.
Bước 8: Bạn có thể đổi tên cột mới này và xóa cột ngày tháng gốc nếu không cần dùng nữa.

Ví dụ: Cột “Tháng” trong bộ dữ liệu của tôi đang bị lẫn lộn thứ tự giữa ngày và tháng. Sau khi tôi nhập định dạng đúng cho 3 dòng đầu tiên, các dòng tiếp theo đã được Power Query tự động đề xuất đúng định dạng mong muốn. Tuy nhiên, đến dòng có giá trị 10/1/2022, do tháng có hai chữ số nên định dạng lại bị sai.

Lúc này, bạn cần tiếp tục nhập ví dụ mẫu cho dòng đó.

Sau khi bổ sung, toàn bộ dữ liệu đã được chuyển thành định dạng đúng như mong muốn (dd/mm/yyyy).

Do đó, điều quan trọng là bạn cần kiểm tra kỹ các giá trị được đề xuất trước khi hoàn tất việc tạo cột mới trong Power Query.

Kết luận

Việc gặp lỗi định dạng ngày tháng trong Power Query khá phổ biến, nhưng may mắn là có nhiều cách để khắc phục. Hy vọng 4 phương pháp trên sẽ hữu ích cho công việc của bạn với Power Query.

Tài liệu tham khảo