Power Query và việc Chuyển đổi “Unpivot” dữ liệu


Liệu cần sắp xếp dữ liệu trong Excel như thế nào cho phù hợp? “Siêu Marketing” qua bài viết sau sẽ giải đáp thắc mắc này bằng việc hướng dẫn unpivot dữ liệu chi tiết, mời quý đọc giả theo dõi.



Việc sắp xếp dữ liệu đóng vai trò cực kỳ quan trọng trong việc phân tích dữ liệu. Khi dữ liệu không được sắp xếp đúng, người dùng sẽ phải vật lộn với những công thức phức tạp, bổ sung cột không cần thiết hoặc phải nhập lại dữ liệu. Nhưng khi mọi thứ được sắp đặt hợp lý, việc sử dụng Excel trở nên đơn giản hơn nhiều. Có lần tôi bất ngờ về khả năng sử dụng công thức phức tạp của một số người dùng. Qua quá trình tìm hiểu, tôi đã học được nhiều mẹo vặt và kỹ thuật. Tuy nhiên, tôi nhận ra rằng chỉ cần có dữ liệu được sắp xếp đúng cách thì ít khi cần đến những kỹ thuật cao cấp đó.


Trước khi Power Pivot xuất hiện, việc biến đổi dữ liệu thường khá tốn thời gian; chúng tôi phải làm việc với các công cụ và mã VBA phức tạp. Nhưng Power Query đã mang tới nhiều chức năng hỗ trợ biến đổi một cách dễ dàng. Một trong số những tính năng hữu dụng nhất là chức năng “unpivot” dữ liệu mà bài viết này sẽ nói tới. Qua ba ví dụ, chúng tôi sẽ đưa các bạn từ những dữ liệu nguồn dễ dàng số liệu chuyển đổi sang những dạng phức tạp hơn. Nhưng các bạn sẽ thấy rõ ràng, chỉ cần vận dụng linh hoạt và chính xác một số chuyển đổi cơ bản, tất cả mọi thứ sẽ trở nên thuận lợi.

Tải về

Để thực hành cùng với các ví dụ dưới đây, hãy tải các tệp mẫu về máy. Bạn có thể click vào đây để đăng ký và truy cập mục Downloads.

Ví dụ trong bài sử dụng tệp có tên Example 12 – Unpivot Data.xlsx

Hãy bắt đầu mở Excel, và mở tệp mẫu. Chúng ta bắt đầu thôi!

Bắt đầu “Unpivot” dữ liệu

Trong bước đầu tiên “unpivot” dữ liệu của chúng ta, chúng ta sẽ làm việc với bảng tính Example 1.

power-query-unpivot-du-lieu-1
power-query-unpivot-du-lieu-1

Dữ liệu ban đầu được sắp xếp thành một cột cho mỗi tháng. Song, thay vì thế, việc có một cột dành cho tên của tháng và một cột giá trị duy nhất sẽ tốt hơn. Đây là mục tiêu cấu trúc dữ liệu mà chúng ta muốn đạt được sau cùng của ví dụ này.

Lựa chọn một ô bất kỳ trong phạm vi dữ liệu và chuyển đổi nó thành bảng bằng cách nhấn vào Insert -> Table (hoặc nhấn phím Ctrl + T).

power-query-unpivot-du-lieu-2
power-query-unpivot-du-lieu-2

Cửa sổ Create Table sẽ xuất hiện để bạn có thể thiết lập vùng dữ liệu và kiểm tra xem có hàng tiêu đề hay không. Dữ liệu nguồn chứa tổng cộng ở hàng 42, thông tin này có thể được loại bỏ khỏi vùng dữ liệu.

Nếu dữ liệu có hàng tiêu đề, chúng ta sẽ chọn tùy chọn Bảng có tiêu đề rồi nhấn OK.


power-query-unpivot-du-lieu-3

power-query-unpivot-du-lieu-3

Click vào một ô bất kỳ trong Table và Table Design ribbon sẽ hiển thị. Hãy đặt tên cho Table (tôi đã đặt là SalesData).

power-query-unpivot-du-lieu-4
power-query-unpivot-du-lieu-4

Khi đã chuẩn bị xong, chúng tôi cho Table SalesData được chuyển vào Power Query bằng cách nhấn Data -> From Table/Range.

power-query-unpivot-du-lieu-5
power-query-unpivot-du-lieu-5

Power Query Editor sẽ được mở và trình bày một xem trước của dữ liệu có sẵn.

power-query-unpivot-du-lieu-6
power-query-unpivot-du-lieu-6

Nếu trong tương lai, dữ liệu có thêm hoặc thay đổi các cột, xóa bước Change Type. Việc này gây ra sự thay đổi tiêu đề cột sang mã M và làm cản trở khả năng tự động điều chỉnh khi có sự thay đổi cấu trúc cột.

Vào Transform -> Unpivot Columns để thực hiện việc chuyển đổi dữ liệu. Có ba lựa chọn ở đây, mà lựa chọn nào sẽ được dùng phụ thuộc vào kết quả mong muốn của người dùng.

power-query-unpivot-du-lieu-7
power-query-unpivot-du-lieu-7

Các lựa chọn này cuối cùng sẽ dẫn đến hai kết quả: có thể unpivot dữ liệu dựa trên cột đã chọn hoặc không chọn. Sự khác biệt là gì và vì sao chúng ta cần quan tâm?

Chẳng hạn, nếu chọn cột Tháng Một, Tháng Hai và Tháng Ba và dùng Unpivot Only Selected Columns, Power Query sẽ không chuyển đổi các cột đó. Thay vào đó, một cột mới tên là Attribute được tạo ra chứa tên của các tháng.

power-query-unpivot-du-lieu-8
power-query-unpivot-du-lieu-8

Mã M cần dùng để thực hiện quá trình biến đổi này sẽ là:

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

Tên cột sau khi chuyển đổi củaTháng Một, Tháng Hai và Tháng Ba đã được đổi tên thành mã M rồi. Vậy nếu chúng ta bổ sung thêm thông tin cho tháng Tư thì sao? Power Query sẽ không thực hiện unpivot cho cột dữ liệu tháng Tư vì nó không nằm trong phạm vi của mã. Tuy nhiên, nếu bạn thêm một cột Attribute mới, mọi thứ vẫn sẽ diễn ra suôn sẻ.

Chúng ta có thể thực hiện việc chọn các cột Khách hàng và Sản phẩm, sau đó áp dụng tính năng Unpivot Other Columns. Trong trường hợp dữ liệu của chúng ta hiện tại, kết quả trực quan trong Cửa sổ xem trước sẽ giống nhau, nhưng mã M lại khác biệt.

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

Mã này không đề cập đến cột tháng cụ thể nên mọi tháng mới được thêm vào sau này đều sẽ đi theo mô hình này. Đây là điều tốt cho cột dữ liệu, nhưng lại không tốt cho cột Thuộc tính / thông số.

Bây giờ, chúng ta hãy tiến hành rút gọn câu lệnh truy vấn bằng cách thực hiện một số biến đổi sau đây:

  • Đổi tên cột Attribute thành Tháng.
  • Chỉnh sửa kiểu dữ liệu cho mỗi cột sao cho phù hợp với thông tin bên trong cột đó (Tôi đã chọn Kiểu Text, Text, Text và Số thập phân).
  • Bộ lọc cần được áp dụng để loại bỏ những con số không từ cột Value.

Sau khi click vào Close & Load, dữ liệu sẽ được nhập vào Excel và bảng tính sẽ trông như hình sau:

power-query-unpivot-du-lieu-10
power-query-unpivot-du-lieu-10

Tiếp theo đó, chúng ta có thể dùng cả công thức và Bảng Pivot để tạo ra các biểu đồ theo ý muốn. Sự linh hoạt trong việc chuẩn bị dữ liệu là rất quan trọng.

Unpivot dữ liệu khi có nhiều cột hơn

Bước vào Ví dụ 2. Đây là dạng dữ liệu nguồn:

power-query-unpivot-du-lieu-11
power-query-unpivot-du-lieu-11

Lúc này, thay vì chỉ một cột cho mỗi tháng, dữ liệu đi kèm với hai cột chỉ ra Giá trị Bán hàng và Số lượng Bán hàng. Cách hiển thị thông tin hợp lẽ nhất là đặt tên của các tháng trong một cột và Giá trị và Số lượng là các cột khác biệt.

Các bước làm việc giống như trong Ví dụ 1:

  • Chuyển dữ liệu thành một Bảng (sử dụng hàng đầu tiên làm tiêu đề và không bao gồm tổng số trong phạm vi dữ liệu).
  • Đặt tên cho bảng.
  • Click vào Data -> From Table/Range để đưa dữ liệu vào Power Query.
  • Để đảm bảo an toàn, hãy loại bỏ bước Change Type tự động được Power Query tạo ra.

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

Giờ đến lúc thực hiện một chuyển đổi mới:

  • Lựa chọn các cột Khách hàng và Sản phẩm, sau đó click vào Transform -> Unpivot Columns (dropdown) -> Unpivot Other Columns.
  • Chọn cột
    Attribute và tách cột đó thành những phần mình cần.

Trong trường hợp ví dụ của chúng ta, chúng ta sẽ chia nó dựa vào ký tự khoảng trống. Nhấn vào Transform -> Split Column -> Split by Delimeter và thực hiện như cài đặt cho phép.

power-query-unpivot-du-lieu-12
power-query-unpivot-du-lieu-12
  • Bây giờ, cột Attribute đã được phân tách thành hai cột.
  • Sau khi lựa chọn Value cùng Unit trong cột hiển thị, ta click vào Transform -> Pivot Column.
  • Tại hộp thoại Pivot Column, thực hiện chọn cột có chứa số liệu và ấn OK.
  • power-query-unpivot-du-lieu-13
    power-query-unpivot-du-lieu-13
  • Đổi tên cột Attribute thành Month.
  • Set kiểu dữ liệu cho từng cột tương ứng.

Ấn vào Close & Load để kết thúc.

Giờ đây, dữ liệu trước khi được chia trong Excel đã sẵn sàng… hoàn toàn xuất sắc!

power-query-unpivot-du-lieu-14
power-query-unpivot-du-lieu-14

Unpivot khi có nhiều hàng tiêu đề

Với ví dụ sau cùng này, tất cả đều tương tự như Example 2, chỉ khác ở phần tiêu đề của bảng. Lúc này, chúng ta có hai hàng tiêu đề: một hiển thị tháng và một hiển thị Value hoặc Unit.

power-query-unpivot-du-lieu-15
power-query-unpivot-du-lieu-15

Chỉ nên có một hàng tiêu đề duy nhất trong Bảng Excel và dữ liệu Power Query. Điều này phản ánh một cách thực hành quản lý dữ liệu tốt. Khi phân chia tiêu đề ra thành hai hàng, việc này gây khó khăn cho việc làm việc với Excel do định dạng trình bày.

Bắt đầu unpivot bằng cách chuyển dữ liệu thành Bảng Excel, nhớ là không chọn tùy chọn MyTable có Headers như các ví dụ trước.

power-query-unpivot-du-lieu-16
power-query-unpivot-du-lieu-16

Một số biến đổi quan trọng sẽ được thực hiện với dữ liệu nguồn sau khi nhấn OK.

  • Một hàng tiêu đề mới chứa Column 1, Column 2, Column 3, v.v. đã được tạo ra 
  • Tất cả các hàng được đẩy xuống một hàng để nhường chỗ cho hàng tiêu đề mới này 
  • Các ô đã được hợp nhất
    • Nội dung đã được chuyển đến ô đầu tiên sau khi thực hiện hợp nhất các ô.
    • power-query-unpivot-du-lieu-17
      power-query-unpivot-du-lieu-17

    Để đưa dữ liệu vào Power Query, chọn một ô nằm trong Bảng và sau đó nhấn vào Data -> From Table/Range.

    Có thể thấy cửa sổ xem trước hiển thị dữ liệu như sau:

    power-query-unpivot-du-lieu-18
    power-query-unpivot-du-lieu-18

    Tuy nhiên, cửa sổ này không thể hiện tháng ở mỗi cột và nếu chúng ta unpivot dữ liệu lúc này, các giá trị null sẽ được điền vào. Do đó, trước hết chúng ta sẽ cần sử dụng chức năng Fill Down để biến đổi dữ liệu thay vì chức năng Fill Across như thường thấy.

    • Chọn Transform -> Transpose
    • Đối với Column 1, chọn Transform -> Fill (drop-down) -> Down
    • Tiếp theo, chọn Column 1 và Column 2
    • Kích vào Transform -> Merge Columns
    • Trong cửa sổ Merge Columns, chọn ký tự phân cách không tồn tại trong văn bản của hai cột. (không gian trống có thể được sử dụng, tuy nhiên có thể không phải là lựa chọn tốt nhất tùy trường hợp)
    • Nhấn Transform -> Transpos để đảo ngược biến đổi và chọn lại Column 1 và Column 2.
    • Click vào Transform -> Format (dropdown) -> Trim để loại bỏ các khoảng trắng thừa từ các cột Khách hàng và Sản phẩm mà chúng ta đã thêm vào ở bước trước.
    • Bây giờ, hãy đặt hàng đầu tiên làm tiêu đề bằng cách kích vào Transform -> Use First Row as Headers

    Sau khi thực hiện những bước biến đổi này, Cửa sổ Xem trước sẽ trông như sau:

    power-query-unpivot-du-lieu-19
    power-query-unpivot-du-lieu-19

    Như vậy, chúng ta đã hoàn thành định dạng như đã thực hiện trong Ví dụ 2. Hãy theo từng bước chuyển đổi trong ví dụ đó để hoàn tất.

    Hẳn bạn đã nắm bắt cách unpivot dữ liệu sử dụng power query sau bài viết này. Siêu Marketing kỳ vọng bạn sẽ luôn gặp thành công khi học và áp dụng Excel vào công việc của mình.

    Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop