4 Cách để sửa Date Errors trong Power Query + Locale & Regional Settings

5/5/2020

Nội dung: Tìm hiểu 4 cách khác nhau để sửa các lỗi trong loại dữ liệu ngày trong Power Query, bao gồm cài đặt địa điểm và địa phương, và custom công thức với Column From Examples.

Trình độ kỹ năng: Trung bình

Sửa Date Errors trong Power Query

Đôi khi trong Power Query, khi bạn cố định dạng dữ liệu như ngày, bạn sẽ nhận thông báo lỗi. Điều này là do Power Query không thể nhận ra dữ liệu. Sự xuất hiện phổ biến nhất cho điều này là khi định dạng ban đầu của ngày là từ một khu vực khác.

Mỹ sử dụng định dạng MM/DD/YYYY, trong khi hầu hết các nước khác trên thế giới sử dụng DD/MM/YYYY. Điều này có thể gây ra vấn đề với việc chuyển đổi ngày trong Excel và Power Query.

Có 4 cách khác nhau chỉ ra vấn đề về các lỗi ngày.

1. Cài đặt địa điểm trong menu Data Type

Cách đầu tiên để khắc phục lỗi ngày xuất phát từ sự khác biệt về vị trí là chọn cài đặt Using Locale trong menu thả xuống Data Type cho bất kỳ cột/query nào có lỗi.

1. Click hộp thoại Data Type trên góc trái phía trên của cột có chứa ngày, sau đó chọn Using Locale…
2. Cửa sổ Change Type with Locale xuất hiện. Tại đây, bạn có thể chọn Date làm Data Type, sau đó cho trường Locale, bạn có thể chọn vị trí nơi dữ liệu bắt nguồn.
3. Nhấn OK để áp dụng để thay đổi loại dữ liệu thành một ngày với định dạng phù hợp.

Định dạng này sẽ cần được thực hiện trên mỗi query trả về lỗi cho các cột ngày. Nếu bạn đang làm việc với một query có một số cột ngày hoặc một tệp có nhiều query, thì bạn có thể muốn sử dụng mẹo tiếp theo thay thế.

2. Vị trí trong cài đặt khu vực

Bạn cũng có thể thay đổi cài đặt khu vực cho toàn bộ tệp của mình. Để làm điều này:

1. Chuyển đến menu File và chọn Options and settings, sau đó Query Options.
2. Điều này xuất hiện cửa sổ Query Options. Chọn Regional Settings, sau đó chọn Locale nơi dữ liệu bắt nguồn từ đó.
3. Nhấn OK để lưu các thay đổi.

Đối với bất kỳ query nào trong tệp này có đặc thù cho một vùng, giờ đây định dạng sẽ hiển thị chính xác khi bạn thay đổi loại dữ liệu của một cột thành Ngày. Bạn KHÔNG cần phải sử dụng tùy chọn Using Locale được giải thích trong mục số 1 ở trên.

Note: Cài đặt này nằm cùng với tệp. Vì vậy, nếu bạn chia sẻ tệp với ai đó từ một quốc gia khác, họ sẽ không phải thay đổi ngôn ngữ để khớp với dữ liệu nguồn. Nếu họ thêm query mới có ngày ở định dạng cục bộ, thì họ sẽ cần thay đổi điều này hoặc sử dụng tùy chọn Using Locale (mục 1 ở trên).

3. Cài đặt khu vực hệ điều hành

Nếu bạn đang muốn áp dụng cài đặt khu vực cho nhiều hơn một tệp cùng một lúc, bạn có thể thay đổi cài đặt khu vực trên hệ điều hành của mình.

Một ví dụ về điều này có thể là nếu bạn làm việc từ xa từ một quốc gia, nhưng các tệp bạn xử lý mỗi ngày đến từ một công ty ở một quốc gia khác. Thay vì thay đổi cài đặt khu vực cho từng tệp riêng lẻ, về cơ bản, bạn cho máy tính của mình biết rằng bạn đang ở quốc gia khác. Bằng cách đó, nó sẽ hiển thị tất cả dữ liệu của bạn theo định dạng của khu vực/địa phương đó.

Để thay đổi cài đặt của bạn trong Windows:

1. Chuyển đến menu Start và bắt đầu nhập từ “regional”. Điều này sẽ mở cửa sổ Cài đặt và điều hướng đến Region settings.
2. Trong Regional format, bạn có thể chọn vị trí/định dạng mà hầu hết dữ liệu của bạn đến từ.
3. Bạn có thể cần phải khởi động lại Excel để các thay đổi có hiệu lực.

Hãy nhớ rằng việc thay đổi cài đặt này có thể có tác động trong các ứng dụng khác ngoài Excel. Ví dụ: đồng hồ hệ thống của bạn có thể chuyển đổi sang định dạng thời gian 24 giờ, v.v.

4. Công thức tùy chỉnh với cột từ các ví dụ

Sửa chữa cuối cùng này là cho ngày được định dạng kỳ lạ. Ví dụ, đây không chỉ là vấn đề về định dạng của người Mỹ so với người Anh, mà có lẽ bạn đã xuất dữ liệu từ một hệ thống dữ liệu theo cách thú vị.

Nếu đó là trường hợp, bạn có thể sử dụng tính năng Column From Examples trong Power Query để thực hiện một phép thuật nhỏ. Tính năng này về cơ bản thực hiện nhận dạng mẫu dựa trên các ví dụ về cách bạn sẽ thay đổi ngày từ định dạng hiện tại sang định dạng mong muốn của bạn.

Để sử dụng ví dụ, nếu ngày hiện được định dạng là [25.11–2018, 29.5–2018, và 7.5–2018], có thể cho Power Query biết rằng những ngày đó sẽ là [11/25/2018, 5/29/2018, và 5/7/2018]. Từ đó, tính năng Columns From Examples sẽ đoán phần còn lại của dữ liệu vì nó có thể chọn ra mẫu trong các thay đổi của tôi.

Để sử dụng tính năng Column From Examples:

1. Chuyển đến tab Add Column trong Query Editor và chọn thả xuống Columns From Examples.
2. Giả sử rằng bạn đã có cột bạn muốn thay đổi được chọn, bạn có thể nhấp vào From Selection. Nếu bạn chọn From All Columns, Power Query sẽ bao gồm tất cả các cột trong phần đánh giá về nội dung bạn nhập tiếp theo.
3. Điều này sẽ mở ra một cột mới và đó là nơi bạn sẽ nhập ngày bằng cách sử dụng định dạng chính xác. Sau khi nhập hai hoặc ba mục nhập, Power Query sẽ bắt đầu đoán định dạng chính xác cho phần còn lại của dữ liệu. Khi bạn có thể nói rằng nó chính xác, bạn có thể nhấp vào OK.
4. Cột mới với ngày được thêm vào cuối bảng.
5. Thay đổi kiểu dữ liệu của cột thành Ngày và đổi tên cột.

Bây giờ bạn có một cột ngày theo định dạng chính xác, bạn có thể xóa cột cũ, nếu bạn muốn.

Nguồn: https://www.excelcampus.com/powerquery/power-query-date-errors-settings/