POWER QUERY – UNPIVOT dữ liệu

Bố cục dữ liệu chính xác cho Excel để sử dụng? Bài viết này của Học Excel Online sẽ cung cấp câu trả lời tốt cho câu hỏi đó, bằng cách hướng dẫn chi tiết các Unpivot dữ liệu mời các bạn cùng đọc. 

Chuẩn bị dữ liệu có lẽ là yếu tố quan trọng nhất trong phân tích dữ liệu. Nếu bố trí dữ liệu sai, bạn sẽ liên tục phải chiến đấu với Excel; phải sử dụng công thức nâng cao, thêm cột không cần thiết hoặc thậm chí trùng lặp dữ liệu. Nhưng nếu dữ liệu ở đúng cấu trúc, Excel sẽ trở nên dễ dàng. Tôi đã từng ngạc nhiên về những người có thể sử dụng các công thức phức tạp. Tôi đã thu thập được rất nhiều mẹo và thủ thuật bằng cách cố gắng học những kỹ thuật tương tự. Nhưng tôi nhận thấy rằng việc nhận dữ liệu ở định dạng chính xác mà tôi thấy hiếm khi cần đến những kỹ năng nâng cao này. 

Trước Power Pivot, chúng ta sẽ mất nhiều thời gian để chuyển đổi dữ liệu; chúng ta đã phải sử dụng đến công cụ phức tạp hoặc mã VBA dài. Nhưng Power Query có nhiều tính năng để giúp việc chuyển đổi này trở nên dễ dàng. Một trong những tính năng hữu ích nhất là unpivot dữ liệu, đây là những gì chúng ta đề cập trong bài đăng này. Qua ba ví dụ, chúng ta sẽ tiến bộ từ các định dạng dữ liệu nguồn dễ đến khó. Nhưng nó sẽ trở nên rõ ràng một nền tảng trong một vài phép chuyển đổi cơ bản được sử dụng đúng cách là tất cả những gì cần thiết.

Downloads

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

Các ví dụ trong bài đăng này sử dụng tệp có tên Example 12 – Unpivot Data.xlsx

Bây giờ hãy khởi động Excel, hãy mở tệp ví dụ. Chúng ta sẵn sàng!

Unpivot dữ liệu cơ bản

Đối với lần đầu tiên chúng ta unpivot dữ liệu, chúng ta đang sử dụng trang tính Example 1.

Dữ liệu nguồn hiển thị một cột riêng cho mỗi tháng. Tuy nhiên, thay vì có một cột cho mỗi tháng, tốt hơn nên có một cột chứa tên tháng làm Attribute  cùng với cột giá trị đơn lẻ. Đây là bố cục dữ liệu mà chúng ta đang hướng tới ở phần cuối của ví dụ này.

Chọn bất kỳ ô nào trong phạm vi dữ liệu và thay đổi nó thành Bảng Excel bằng cách nhấp vào Insert -> Table (hoặc nhấn Ctrl + T)

Cửa sổ Create Table sẽ đtạo vùng dữ liệu và nếu có hàng tiêu đề. Thông tin nguồn bao gồm tổng số trong hàng 42, chúng ta có thể loại trừ thông tin này khỏi vùng dữ dữ liệu.

Vì dữ liệu của chúng ta có một hàng tiêu đề duy nhất, chúng ta có thể chọn tùy chọn Bảng của tôi có tiêu đề, sau đó nhấp vào OK.

Bấm vào bất kỳ ô nào trong Bảng, ribbon cửa sổ Table Design sẽ xuất hiện. Đặt cho Bảng một cái tên (Tôi đã sử dụng SalesData).

Bây giờ chúng ta đã sẵn sàng tải Bảng SalesData vào Power Query. Bấm Data -> From Table/Range.

Power Query Editor mở ra hiển thị bản xem trước của dữ liệu.

Nếu tại bất kỳ thời điểm nào trong tương lai có thể có nhiều cột hơn hoặc nhiều cột khác nhau, thì hãy xóa bước Change Type. Bước này thay thế các tiêu đề cột thành mã M, do đó sẽ không tự động mở rộng hoặc rút lại đối với các bố cục cột khác nhau.

OK, bây giờ cho việc hủy chia sẻ diễn ra. Trong nút Transform -> Unpivot Columns có ba tùy chọn, tùy chọn chúng ta chọn tùy thuộc vào kết quả chúng ta muốn.

Từ ba lựa chọn đó, thực sự chỉ có hai kết quả; chúng ta có thể unpivot dữ liệu của các cột đã chọn hoặc không được chọn. Sự khác biệt là gì, và nó có quan trọng không?

Nếu chúng ta chọn cột January, February và March và áp dụng Unpivot Only Selected Columns, thì nó sẽ không chia trên các cột đó. Power Query tạo một cột mới có tên Attribute  chứa các tháng.

Mã M để đạt được sự chuyển đổi này sẽ là:

= Table.Unpivot(#”Changed Type”, {“January”, “February”, “March”}, “Attribute”, “Value”)

Tiêu đề cột của January, February và March được thay thế thành mã M. Vậy điều gì sẽ xảy ra nếu chúng ta thêm dữ liệu bổ sung cho April? Power Query sẽ không unpivot dữ liệu cột April vì nó không được bao gồm trong mã. Nhưng nếu bạn có khả năng thêm một cột Attribute  mới thì nó sẽ hoạt động tốt.

Thay vào đó, chúng ta có thể chọn các cột Customer và Product và sử dụng tùy chọn Unpivot Other Columns. Đối với tập dữ liệu hiện tại của chúng ta, nó đạt được cùng một kết quả trực quan trong Cửa sổ xem trước, nhưng mã M thì khác.

= Table.UnpivotOtherColumns(#”Changed Type”, {“Customer”, “Product”}, “Attribute”, “Value”)

Mã không tham chiếu đến các cột tháng; do đó bất kỳ tháng nào được thêm vào cũng sẽ không được chia. Điều này rất tốt cho các cột dữ liệu, nhưng không tốt cho các cột Thuộc tính / kích thước.

Bây giờ hãy thu gọn truy vấn với các phép biến đổi sau:

• Thay đổi tiêu đề của cột Attribute  thành Month.  
• Thay đổi kiểu dữ liệu của mỗi cột để phù hợp với dữ liệu trong cột (Tôi đã chọn Văn bản, Văn bản, Văn bản, Số thập phân). 
•  Lọc để loại bỏ các số không khỏi cột Value.

Nhấp vào Close & Load để đẩy dữ liệu vào Excel. Trang tính sẽ giống như sau:

Từ đó, chúng ta có thể sử dụng công thức hoặc Pivot Table để tạo bất kỳ dạng nào chúng ta muốn.  Đây là sức mạnh mà việc chuẩn bị dữ liệu mang lại cho chúng ta.

Unpivot dữ liệu với nhiều cột

Bây giờ là lúc cho Example 2. Dữ liệu nguồn trông như thế này:

Thay vì một cột cho mỗi tháng, giờ đây chúng ta có hai cột, hiển thị Sales Value và Sales Unit. Định dạng hữu ích nhất là đặt tên tháng trong một cột với Value và Unitị là các cột riêng biệt.

Các bước bắt đầu giống như Example 1:

• Chuyển đổi dữ liệu thành Bảng (sử dụng hàng đầu tiên làm tiêu đề và loại trừ tổng số khỏi vùng dữ liệu).  
• Đặt tên cho bảng.  
• Nhấp vào Data -> From Table/Range để tải dữ liệu vào Power Query. 
• Để an toàn, hãy xóa bước Change Type được tạo bởi Power Query tự động.

Cửa sổ xem trước như sau:

Bây giờ chúng ta cần một chuyển đổi:

• Chọn các cột Customer và Product và nhấp vào Transform -> Unpivot Columns (dropdown) -> Unpivot Other Columns. 
• Chọn cột Attribute và chia cột thành các phần chúng ta cần.  

Đối với ví dụ của chúng ta, chúng ta cần chia theo ký tự khoảng trắng. Bấm Transform -> Split Column ->  Split by Delimeter. Sau đó, áp dụng các cài đặt bên dưới.

• Cột Attribute bây giờ sẽ được chia thành hai cột riêng biệt. 
• Chọn cột hiển thị Value và Unit, sau đó bấm Transform -> Pivot Column. 
• Trong cửa sổ Pivot Column, chọn cột chứa các số, sau đó bấm OK.

• 

• Thay đổi tên của cột Attribute thành Month. 
• Đặt kiểu dữ liệu cho mỗi cột.

Cuối cùng nhấp Close & Load.

Dữ liệu chưa được chia trong Excel bây giờ trông như thế này… hoàn hảo!

Unpivot với nhiều hàng tiêu đề

Bây giờ là ví dụ cuối cùng của chúng ta. Mọi thứ đều giống như Example 2, ngoại trừ các tiêu đề bảng. Bây giờ chúng ta có hai hàng tiêu đề, hàng đầu tiên hiển thị tháng và hàng thứ hai hiển thị Value hoặc Unit.

Bảng Excel và dữ liệu trong Power Query chỉ có thể có một hàng tiêu đề duy nhất. Đây là thực hành quản lý dữ liệu tốt. Bằng cách tách tiêu đề thành hai hàng, nó trở thành một định dạng trình bày rất khó làm việc trong Excel.

Hãy bắt đầu việc unpivot dữ liệu bằng cách chuyển dữ liệu này thành Bảng Excel. Có một điểm khác biệt quan trọng đối với các ví dụ trước đó, không chọn tùy chọn MyTable có Headers.

Sau khi nhấp vào OK, Một số thay đổi quan trọng sẽ được thực hiện đối với dữ liệu nguồn.

• Một hàng tiêu đề hiển thị Column 1, Column 2, Column 3, v.v. đã được thêm 
• Tất cả các hàng đã được di chuyển xuống một hàng để tạo khoảng trống cho hàng tiêu đề mới 
• Các ô được hợp nhất đã xóa hợp nhất, với nội dung được đặt vào ô đầu tiên của mỗi  khu vực hợp nhất.

• 

Nhập dữ liệu vào Power Query bằng cách chọn bất kỳ ô nào trong Bảng, sau đó nhấp vào Data -> From Table/Range.

Cửa sổ xem trước hiển thị như sau:

Quan điểm này cho chúng ta một vấn đề; nó không hiển thị tháng trong mỗi cột. Nếu chúng ta cố gắng unpivot dữ liệu, nó sẽ unpivot dữ liệu với giá trị null. Đầu tiên chúng ta cần sử dụng phép chuyển đổi Fill Across… nhưng không có! Thay vào đó, chúng ta cần chuyển đổi dữ liệu, Fill Down, thực hiện một số phép biến đổi, sau đó chuyển đổi lại một lần nữa.

• Nhấp vào Transform -> Transpose
• Chọn Column 1, nhấp vào Transform -> Fill (drop-down) -> Down
• Chọn Column 1 và Column 2 
• Nhấp vào Transform -> Merge Columns 
• Trong cửa sổ Merge Columns, chọn dấu phân tách không có trong văn bản của hai cột.  (Tôi đã tìm một khoảng trống, nhưng đây có thể không phải là lựa chọn tốt nhất cho trường hợp của bạn) 
• Nhấp vào Transform -> Transpos để đưa dữ liệu trở lại định dạng trước đó Chọn Column 1 và Column 2.
• Nhấp vào Transform -> Format (dropdown) -> Trim để xóa ký tự khoảng trắng khỏi các cột Customer và Product mà chúng tôi đã thêm ở bước trước 
• Bây giờ hãy đưa hàng đầu tiên làm tiêu đề bằng cách nhấp vàoTransform -> Use First Row as Headers

Sau tất cả những biến đổi đó, Cửa sổ Xem trước sẽ trông như thế này:

Bây giờ nó có định dạng giống như chúng ta đã có trong Example 2. Để hoàn thành ví dụ này, hãy làm theo tất cả các bước chuyển đổi từ ví dụ đó.

Chắc hẳn qua bài viết này các bạn đã có thể dễ dàng unpivot dữ liệu với power query rồi. Học Excel Online chúc các bạn luôn thành công trong việc học và ứng dụng Excel của mình.