Chia sẻ cách xử lý dữ liệu ngày tháng bị sai định dạng hoặc thiếu năm trong Power Query

Chào mọi người,

Mình thường xuyên phải làm việc với các file Excel mà dữ liệu ngày tháng nhập vào rất lộn xộn. Có lúc thì thiếu năm, có lúc thì sai định dạng (ví dụ: 31/12/2023 hoặc 12-31-2023). Việc xử lý thủ công rất mất thời gian và dễ sai sót. Gần đây, mình có tìm hiểu và áp dụng Power Query để giải quyết vấn đề này khá hiệu quả, nên muốn chia sẻ lại cho anh em nào đang gặp tình trạng tương tự.

Tình huống:

• Dữ liệu ngày tháng có thể ở dạng dd/mm/yyyy, mm/dd/yyyy, hoặc thậm chí chỉ có dd/mm (thiếu năm).
• Năm có thể là 2 số (23 thay vì 2023).
• Cần chuẩn hóa về một định dạng thống nhất và đảm bảo tính chính xác.

Cách làm với Power Query:

1. Load dữ liệu vào Power Query: Chọn vùng dữ liệu, vào tab Data -> From Table/Range.
2. Kiểm tra kiểu dữ liệu: Power Query thường tự nhận diện kiểu ngày tháng, nhưng đôi khi nó nhận sai. Nếu cột ngày tháng chưa đúng kiểu Date, hãy click vào biểu tượng bên trái tên cột và chọn Date.
3. Xử lý định dạng không nhất quán: Nếu có nhiều định dạng khác nhau, cách đơn giản là Replace Values. Ví dụ, thay thế - bằng /.
4. Xử lý thiếu năm: Đây là phần quan trọng. Nếu cột ngày tháng của bạn bị thiếu năm, bạn có thể dùng Add Column -> Custom Column với công thức tương tự như sau (giả sử cột ngày tháng là [DateColumn] và bạn muốn mặc định năm là 2023 nếu thiếu):

   if Date.Year([DateColumn]) = 1899 // Hoặc một giá trị mặc định khác mà PQ hay nhận sai cho ngày thiếu năm 

   then Date.AddYears([DateColumn], 2023 - Date.Year(DateTime.LocalNow())) // Hoặc năm cụ thể bạn muốn

     else [DateColumn]

5. Chuẩn hóa năm (nếu cần): Nếu năm chỉ có 2 chữ số (ví dụ: 23), bạn có thể cần một bước xử lý khác tùy thuộc vào logic bạn muốn (ví dụ: nếu năm nhỏ hơn 30 thì cộng 2000, nếu lớn hơn thì trừ 100, v.v.). Tuy nhiên, thường Power Query sẽ tự xử lý tốt nếu định dạng ban đầu rõ ràng.
6. Đóng và Load: Sau khi xử lý xong, vào Home -> Close & Load To... để đưa dữ liệu về Excel.

Mẹo nhỏ là hãy luôn kiểm tra kỹ các bước Applied Steps trong Power Query để đảm bảo mọi thứ diễn ra đúng như mong đợi.

Hy vọng chia sẻ này hữu ích với mọi người!

Hay quá bạn ơi! Mình cũng hay gặp cảnh dữ liệu ngày tháng "trời ơi đất hỡi" thế này. Power Query đúng là cứu cánh thật. Bạn có thể chia sẻ chi tiết hơn về cách bạn xử lý trường hợp chỉ có dd/mm mà thiếu năm không? Mình hay phải tự thêm năm thủ công, khá mất công.

Cảm ơn bạn đã chia sẻ cách xử lý dữ liệu ngày tháng bằng Power Query, rất hữu ích cho mọi người!

Đúng là dữ liệu ngày tháng lộn xộn là nỗi ám ảnh của nhiều người. Mình cũng hay gặp trường hợp như bạn mô tả.

Đối với trường hợp chỉ có dd/mm mà thiếu năm, bạn có thể thử dùng hàm Date.AddYears để thêm năm vào, hoặc đơn giản là sử dụng Date.FromText với một định dạng mặc định mà bạn tin là đúng. Nếu dữ liệu có quy luật, ví dụ luôn là năm hiện tại hoặc năm trước, thì việc xử lý sẽ dễ dàng hơn nhiều.

Mình cũng muốn hỏi thêm, nếu dữ liệu có lẫn cả định dạng mm/dd/yyyy và dd/mm/yyyy thì bạn xử lý như thế nào để Power Query nhận diện đúng ạ? Đôi khi hai định dạng này dễ gây nhầm lẫn.