Lỗi định dạng Date trong Power Query và cách khắc phục

Tóm tắt: Tìm hiểu về các cách khác nhau để sửa lỗi định dạng Date trong Power Query, bao gồm cái đặt về ngôn ngữ, vùng và các công thức tùy chỉnh với cột trong các ví dụ.Cấp độ kĩ năng: Trung bìnhXem video hướng dẫn và Download tệp excel thực hành Tại đây

Lỗi định dạng Date trong Power Query

Đôi khi trong Power Query, khi bạn cố gắng định dạng dữ liệu dưới dạng ngày tháng, bạn sẽ nhận được thông báo lỗi. Điều này là do Power Query không thể nhận dạng dữ liệu. Điều này xảy ra phổ biến nhất là khi định dạng ban đầu của ngày là từ một khu vực khác.Hoa Kỳ sử dụng định dạng MM / DD / YYYY cho ngày tháng, trong khi hầu hết các quốc gia còn lại trên thế giới sử dụng DD / MM / YYYY. Điều này có thể tạo ra sự cố với chuyển đổi ngày trong Excel và Power Query.

Cách khắc phục lỗi Date trong Power Query 

Có 4 cách khác nhau để giải quyết vấn đề lỗi định dạng Date này.

1. Locale trong Menu Data Type

Cách đầu tiên để sửa lỗi ngày bắt nguồn 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 / truy vấn nào có lỗi.

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

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

2. Locale trong Regional Settings

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 File menu và chọn Options and settings, sau đó chọn Options.
2. Thao tác này sẽ hiển thị cửa sổ Options. Chọn Regional Settings trong phần Curent File, sau đó chọn Locale for import cho nơi bắt nguồn dữ liệu.
3. Nhấn OK để lưu các thay đổi.

Đối với bất kỳ truy vấn nào trong tệp này có đặc điểm riêng cho một vùng, giờ đây định dạng sẽ hiển thị chính xác khi bạn thay đổi kiểu dữ liệu của cột thành Ngày. Bạn KHÔNG phải sử dụng tùy chọn Using Locale được giải thích trong phần 1 ở trên.Lưu ý: Cài đặt này vẫn tồn tại với tệp. Vì vậy, nếu bạn chia sẻ tệp với ai đó 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 các truy vấn mới với ngày tháng ở đị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 (Phần 1 ở trên).

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

Nếu bạn đang tìm cách áp dụng cài đặt vùng cho nhiều tệp cùng một lúc, bạn có thể thay đổi cài đặt vùng trên hệ điều hành của mình.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ý hàng ngày đến từ một công ty ở 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 đang thông báo cho máy tính của mình 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 vùng / đị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”. Thao tác này sẽ mở cửa sổ Cài đặt và điều hướng đến Set regional format.
2. Trong menu thả xuống 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 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ể ảnh hưởng đến 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ờ (quân sự), v.v.

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

Cách sửa lỗi định dạng cuối cùng này dùng cho những ngày được định dạng kỳ lạ. Ví dụ: vấn đề không chỉ là sở thích của người Mỹ so với người Anh trong việc định dạng, mà có thể bạn đã xuất dữ liệu từ một hệ thống lấy ra dữ liệu một cách kì quặc.Nếu đúng như vậy, bạn có thể sử dụng tính năng Column From Examples trong Power Query để chỉnh sửa lỗi này. Tính năng này về cơ bản thực hiện nhận dạng khuôn 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.Ví dụ ở đây là nếu ngày của tôi hiện được định dạng là [5/24/2018, 7/14/2018 và 1/24/2018], tôi có thể nói với Power Query rằng những ngày đó phải là [24/5/2018, 14/7/2018 và 24/1/2018]. Từ đó, tính năng Column 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 menu thả xuống Column From Examples.
2. Giả sử rằng bạn đã chọn cột muốn thay đổi, 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 và đánh giá những gì bạn nhập tiếp theo. Chúng tôi chỉ muốn đánh giá cột ngày cho ví dụ này.
3. Thao tác này sẽ mở ra một cột mới và đó là nơi bạn sẽ nhập ngày bằng correct formatting. 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 tháng được thêm vào cuối bảng.
5. Thay đổi kiểu dữ liệu của cột thành Date và đổi tên cột.

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

• Đọc thêm các bài viết chia sẻ kiến thức về phân tích dữ liệu: https://datapot.vn/blog/
• Chuỗi Video Hướng dẫn thực hành Lab và sử dụng các tài nguyên của Microsoft: https://www.youtube.com/c/Datapotvn/videos
• Update tài nguyên từ Microsoft, PL-300 exam questions và exam topics tại Fanpage của Datapot: https://www.facebook.com/DatapotAnalytics/