Power Query – Bỏ sao lưu dữ liệu trong một cột

Chắc hẳn bạn cũng đã có một lần vướng mắc vì  kiểu định dạng dữ liệu khá phổ biến và khó chịu trong Power Query là dữ liệu được xếp chồng lên nhau trong một cột. Trong bài viết này, Học Excel Online sẽ giúp bạn tìm hiểu một phương pháp cơ bản để bỏ sao lưu dữ liệu trong một cột. 

Mặc dù ví dụ mà chúng ta đang xem xét bên dưới rất có thể được tạo ra bởi một người không biết cách cấu trúc dữ liệu chính xác, nhưng định dạng này có thể được tìm thấy như nhau trong dữ liệu được xuất hoặc sao chép từ hệ thống IT (mặc dù dữ liệu được xuất hoặc sao chép có xu hướng thành một cột duy nhất, dễ xử lý hơn một chút).

Ảnh chụp màn hình bên dưới cho thấy địa chỉ (address) có dữ liệu được xếp chồng lên nhau trong một cột

Như bạn có thể tưởng tượng, thao tác dữ liệu ở trong bố cục này đặc biệt khó khăn. Để làm cho nó có thể hoạt động được, mỗi hàng của Address phải là một cột riêng biệt. Đây là lúc cần Power Query đến giải cứu một lần nữa.

Tải xuống tệp ví dụ

Để làm việc cùng với các ví dụ bên dưới, hãy tải xuống tệp mẫu. Nhấp vào đây để đăng ký và có quyền truy cập vào phần Tải xuống.

Các ví dụ về bỏ sao lưu dữ liệu trong bài viết này sử dụng Example 13 – Unstack data.xlsx 

Bỏ sao lưu dữ liệu một cột

Mở trang tính Ví dụ 1 từ tệp đã tải xuống. Dữ liệu sẽ trông giống như ảnh chụp màn hình mà chúng ta đã thấy ở trên.

Dữ liệu đã có ở định dạng Bảng(Table) mà mình đã đặt tên là Customers. Để nhập dữ liệu vào Power Query, hãy chọn bất kỳ ô nào trong Bảng và nhấp vào Data -> From Table/Range

Cửa sổ Power Query sẽ mở ra và hiển thị dữ liệu, trông giống như màn hình bên dưới:

Dọn dẹp các dữ liệu được nhập

Bây giờ chúng ta hãy tách các dòng địa chỉ riêng biệt thành các cột. Trước tiên, hãy xóa bất kỳ hàng trống nào bằng cách lọc cột Address để loại bỏ các giá trị rỗng(null)

Nhấp vào cột Customer, sau đó nhấp vào Transform -> Fill (dropdown) -> Down. Điều này sẽ đảm bảo mỗi hàng sẽ có tên khách hàng trong đó, không được có bất kỳ giá trị rỗng nào trong cột Customer.

Thêm cột Index và Modulo

Trong vài lần biến đổi tiếp theo này, mọi thứ bắt đầu có vẻ hơi kỳ quặc. Tuy nhiên bạn cứ làm theo hướng dẫn của mình, đến cuối cùng mọi thứ sẽ hoàn tất và bạn sẽ hiểu. Tin mình đi!

Chuẩn hóa dữ liệu với power Query

Có 3 hàng cho mọi địa chỉ khách hàng. Các phép biến đổi tiếp theo của chúng ta là bao gồm một cột số hiển thị các số 0, 1 và 2 lặp đi lặp lại. 0 sẽ đại diện cho dòng đầu tiên của địa chỉ, 1 dòng thứ hai của địa chỉ và 2 là dòng cuối cùng của địa chỉ.

Nhấp vào  Add Column -> Index Column

Một cột chỉ mục đã được thêm vào, bắt đầu từ 0.

Chọn cột index mới này, sau đó nhấp vào Add Column -> Standard -> Modulo.

Nhập 3 vào cửa sổ Modulo, vì có 3 hàng trong mỗi địa chỉ. Bấm OK.

Nếu bạn đã làm theo tất cả các bước trên thì Cửa sổ xem trước sẽ giống như ảnh chụp màn hình bên dưới:

Chọn cột Modulo mà chúng ta đã tạo. Nhấp vào Transform -> Pivot Column.. Trong cửa sổ Pivot Column, chọn cột Address, mở rộng các tùy chọn nâng cao và chọn Don’t Aggregate, sau đó nhấp vào OK.

Hãy nhìn vào cửa sổ xem trước, bạn có thể nghĩ rằng chúng ta đang làm rối tung mọi thứ, cứ bình tĩnh, tất cả đều chỉ là một phần của quá trình. Tiếp tục thôi nào.

Chọn các cột có tiêu đề 1 và 2, sau đó áp dụng phép biến đổi  Transform -> Fill Down 

Chọn cột 0 và bộ lọc để loại bỏ các giá trị rỗng. Giờ đây, Cửa sổ Xem trước sẽ hiển thị dữ liệu ở định dạng sau:

Ủa phép thuật hả? Từ một tập dữ liệu bừa bộn sang một tập dữ liệu đẹp và gọn gàng chỉ trong một vài phép biến đổi cơ bản :))

Một trong những kỹ năng quan trọng đối với Power Query là khả năng thực hiện nhiều biến đổi nhỏ mà cuối cùng việc bổ sung sẽ đưa dữ liệu vào đúng định dạng. Thông thường, một cái gì đó có vẻ giống như một sự biến đổi nhỏ, chẳng hạn như việc giải nén dữ liệu, có thể yêu cầu nhiều bước nhỏ hơn.

Kết thúc việc bỏ sao lưu dữ liệu. 

Bạn chỉ cần thực hiện một số biến đổi đơn giản còn lại để thu gọn mọi thứ:

• Loại bỏ cột chỉ mục (Chọn cột và nhấp vào Home -> Remove Columns)
• Đổi tên các cột 0, 1 và 2 (bấm đúp vào từng tiêu đề cột và đặt tên có ý nghĩa).

Dữ liệu hiện đã sẵn sàng. Nhấp vào Close and Load  để đẩy dữ liệu vào Excel.

Trong bài viết này, chúng ta đã thấy cách tách một cột dữ liệu. Nhưng quan trọng hơn, chúng ta đã thấy rằng những kết quả có vẻ cơ bản nhưng lại khá phức tạp. Hiểu được các phép biến đổi đơn giản và cách kết hợp chúng với nhau không phải dễ dàng, bạn cần thời gian nghiên cứu thật kỹ và thực hành.

Ngoài ra vẫn có những phương pháp khác để bỏ sao lưu dữ liệu, ít bước hơn nhưng lại khá phức tạp để hiểu đối với những người không chuyên về Excel. Hẹn gặp lại các bạn trong những bài viết tới của Học Excel Online nhé!