Power BI, Power BI Basics

Unpivot: Chuyển đổi dữ liệu từ định dạng rộng sang dạng dài

KPIM

Cập nhật gần nhất: 21/03/2024

Ngày đăng bài: 21/08/2020

Bạn có gặp phải những câu hỏi này trong Excel hoặc Power BI hoặc Power Query không?

Làm cách nào để chuyển đổi tập dữ liệu Excel có 133+ hàng x 55+ cột thành một tập dữ liệu thích hợp để phân tích?
Unpivot các cột là gì?
Unpivot các cột khác là gì?

Hãy hiểu vấn đề mà chúng ta gặp phải với Dữ liệu của mình.

Trước hết, hãy xem xét cấu trúc của hai tập dữ liệu sau:

Quan trọng: Bảng đầu tiên là Crosstab. Đây không phải là cấu trúc thân thiện với Pivot Table, nhưng bảng thứ hai thì có.

Tình huống: Bạn nhận được bảng nhiều cột. Bạn cần thay đổi thành các bảng có các Cột riêng biệt cho Tên và Giá trị. Tính năng Unpivot Columns biến nhiều tiêu đề cột thành một cột duy nhất nhưng theo hàng. Các giá trị được lưu trữ dưới các cột ban đầu trong một cột khác.

Đây là một ví dụ đơn giản về Unpivot nhiều cột trong Power BI.

Tính năng Unpivot Columns chuyển đổi các tiêu đề dữ liệu được lưu trữ theo chiều ngang thành định dạng dọc, tức là Power Query Column thành Rows. Các cột đã chọn sau đó được chia thành hai cột, ví dụ Thuộc tính và Giá trị.

Thuộc tính đại diện cho tiêu đề của các cột được chọn cho Power BI Unpivot Columns
Giá trị đại diện cho giá trị đã có trong cột của tiêu đề trước đây

Nói cách khác, dữ liệu được xoay từ ngang sang dọc khi áp dụng tính năng Unpivot Columns. Tập dữ liệu ở đây được cấu trúc lại trong vài giây so với việc sao chép thủ công trong Excel, mất hàng giờ.

'Pivot nhiều cột' và 'Unpivot nhiều cột' là hai tính năng mạnh mẽ của Power BI. Những điều này rất hữu ích trong:

Chuyển đổi Dữ liệu của bạn ở định dạng bảng chéo và định dạng tệp phẳng
Xử lý phân tích dữ liệu và hệ thống quản lý cơ sở dữ liệu

Trong bài đăng này, chúng ta sẽ tìm hiểu về các bước cơ bản về cách sử dụng tính năng Power BI Unpivot Columns để Unpivot cột thành hàng. Sau đó, chúng ta sẽ tìm hiểu về bốn trường hợp nâng cao trong đó bạn có thể sử dụng Unpivot cột để chuyển đổi trường dữ liệu cột thành trường dữ liệu hàng.

Trường hợp 1: Unpivot đơn giản

Hãy xem xét rằng chúng ta có một bảng dữ liệu như hình dưới đây - đó chính là câu trả lời của người dùng đối với hai câu hỏi:

"Chúng tôi muốn chuyển đổi cấu trúc bảng và Unpivot dữ liệu ở dạng bảng."

Các bước để chia nhỏ bảng dữ liệu đơn giản này trong Power Query Editor

Bước 1:

Để Unpivot Power BI Desktop, trước tiên bạn cần nhập dữ liệu vào Power BI

Để nhập, trước tiên hãy mở Power BI Desktop và nhấp vào tab Home (Trang chủ)
Sau đó, nhấp vào Get Data (Lấy dữ liệu). Thao tác này sẽ hiển thị danh sách Thả xuống để chọn nguồn Cơ sở dữ liệu
Nhấp vào bất kỳ nguồn dữ liệu nào mà bạn muốn trích xuất dữ liệu. Ở đây chọn tùy chọn Excel như được hiển thị trong hình ảnh bên dưới

Bước 2:

Tải dữ liệu trong Power Query Editor

Đi tới Tab Home
Nhấp vào tùy chọn Edit Queries (Chỉnh sửa truy vấn)
Tải dữ liệu trong Cửa sổ Power Query Editor

Khi bảng dữ liệu của bạn (Crosstab) được mở trong Trình soạn thảo Truy vấn Power Query Editor, đôi khi Tiêu đề của bảng hiển thị Cột1, Cột2, Cột3, v.v.

Bước 3:

Để lấy lại tên cột ban đầu, hãy thực hiện các bước sau

Trước tiên, hãy chuyển đến tab Transform (Chuyển đổi) trong Power Query Editor
Nó chứa hai tùy chọn trong danh sách thả xuống. Nhấp vào tùy chọn đầu tiên, tức là Use First Row as Headers (Sử dụng Hàng đầu tiên làm Tiêu đề). Điều này sẽ đặt hàng dưới dạng tiêu đề và do đó, tên tiêu đề cột ban đầu sẽ được khôi phục tự động

Bước 4:

Sau khi khôi phục chính xác tên tiêu đề của cột ban đầu, hãy chọn các cột bạn muốn Unpivot

Trong trường hợp của chúng tôi, chúng tôi muốn hai câu hỏi xuất hiện theo chiều dọc thành nhiều hàng bên cạnh tên của mọi người. Do đó, ta cần chọn hai cột đại diện cho những câu hỏi này.

Đầu tiên, hãy chọn các cột để Unpivot dữ liệu. Để chọn cột, hãy nhấp vào Tiêu đề của cột đầu
Bây giờ, chuyển đến tab Transform
Nhấp vào mũi tên thả xuống và chọn Unpivot Columns (Unpivot cột), hoặc Chỉ Unpivot các cột đã chọn (Chỉ Unpivot các cột đã chọn) như bên dưới. Tại đây, chúng ta đang chọn tùy chọn Chỉ Unpivot các cột đã chọn để Unpivot dữ liệu đã chọn

Lưu ý: Chúng tôi chưa chọn cột tên (cột đầu tiên) trong đây. Nó đã được đặt theo chiều dọc.

Bước 5:

Thay đổi tên tiêu đề

Ở đây, tiêu đề của các cột đã thay đổi. Tiêu đề thay đổi thành Thuộc tính (đại diện cho tiêu đề ngang ban đầu) và Giá trị. Bạn có thể thay đổi tên của tiêu đề theo nhu cầu của bạn. Trong trường hợp này, chúng tôi sẽ thay đổi tiêu đề của các cột tương ứng là "Tuyên bố" và "Phản hồi".

Để thay đổi tên cột, hãy nhấp chuột phải vào tiêu đề của cột để chọn tuỳ chọn Rename (Đổi tên). Tùy chọn đổi tên sẽ cho phép bạn chỉnh sửa tên cột hoặc nhấp đúp vào tiêu đề của cột để thay đổi tên cột. Sau khi nhập tên cột, nhấn Enter.

Bước 6:

Bổ sung thêm hàng vào dữ liệu ban đầu

Nếu bạn có nhiều hàng hơn được thêm vào dữ liệu ban đầu, bạn không phải lặp lại toàn bộ quy trình này. Thay vì tạo lại truy vấn từ đầu, hãy sử dụng tùy chọn Làm mới dữ liệu.

Để sử dụng nút Làm mới, hãy làm theo các bước như hình dưới đây:

Đi tới tab Home trong Power Query Editor
Nhấp vào Refresh Preview. Thao tác này sẽ cập nhật dữ liệu của bạn trong Power Query Editor như được hiển thị trong hình ảnh bên dưới

Kiểm tra các bước đã tạo trong các bước áp dụng trong Query Settings (Cài đặt Truy vấn).
Để quay lại bước trước, chúng ta cần xóa bước hiện tại. Nhấp vào Tùy chọn Xóa có ở phía trước các bước áp dụng. Nó hoạt động giống như tính năng Hoàn tác (Ctrl + Z).

Trường hợp 2: Unpivot dữ liệu nhiều cột

Xem xét rằng chúng ta có một bảng dữ liệu như hình dưới đây, trong đó:

Dữ liệu là một crosstab.
Các tiêu đề cột đại diện cho hai thuộc tính tức là loại dữ liệu quảng cáo năm (ví dụ: tiền lương, ưu đãi). Nói cách khác, đó là một tiêu đề kết hợp.

Hãy xem cách xử lý với Power BI Unpivot Multiple Columns.

Các bước để Unpivot dữ liệu nhiều cột

Ở đây chúng tôi muốn Unpivot hai cột riêng biệt.

Bước 1:

Như đã giải thích trong Trường hợp 1, trước tiên hãy nhập dữ liệu vào Power BI

Đi tới tab Home trong cửa sổ Power Query Editor
Sau đó, nhấp vào Get Data (Lấy dữ liệu) - Thao tác này sẽ hiển thị danh sách Thả xuống để chọn nguồn Cơ sở dữ liệu
Nhấp vào bất kỳ nguồn dữ liệu nào mà bạn muốn trích xuất dữ liệu. Ở đây chúng tôi đang chọn tùy chọn Excel như được hiển thị trong hình ảnh bên dưới

Bước 2:

Tải dữ liệu trong Power Query Editor

Đi tới tab Home
Nhấp vào tùy chọn Edit Queries (Chỉnh sửa Truy vấn)
Tải dữ liệu trong Cửa sổ Power Query Editor

Bước 3:

Chọn các cột mà bạn muốn áp dụng Unpivot. Ở đây, chúng tôi đang chọn các Cột bên dưới:

2016Salary p.a.
2017Salary p.a.
2016Incentives p.a.
2017Incentives p.a.

Bước 4:

Tất cả các cột này đều chứa các giá trị. Do đó, chúng tôi chỉ áp dụng Unpivot trên các cột này. Để áp dụng Unpivot, hãy làm như sau:

Chọn các cột để Unpivot dữ liệu nhiều cột
Nhấp vào tab Transform trong Power Query Editor
Chuyển đến danh sách thả xuống của Unpivot Columns (Unpivot Cột)
Nhấp vào Unpivot Only Selected Columns (Chỉ Unpivot các cột đã chọn)

Bước 5:

Bây giờ bạn cần Tách các cột chứa các giá trị được kết hợp. Ở đây cột Thuộc tính chứa các giá trị được kết hợp. Do đó, hãy chọn cột thuộc tính và làm theo các bước bên dưới để tách:

Đi tới tab Home trong ruy-băng
Chọn tùy chọn Split Column (Tách cột) tại đây
Nhấp vào nút thả xuống để chọn tùy chọn By Number of Characters (Theo số ký tự). Điều này là do tiền tố năm đại diện cho 4 chữ số và vẫn nhất quán trong cột Thuộc tính. Nếu có dấu gạch ngang giữa năm và mức lương / khuyến khích, chúng tôi sẽ cần chọn By Delimiter (Theo Dấu phân cách).

Một cửa sổ mới mở ra sau khi nhấp vào nút By Number of Characters. Nhập số ký tự tại đây. Điều này sẽ chia các cột. Ví dụ: tách sau 4 ký tự đầu tiên, tức là năm (2016, 2017, v.v.)
Trong cửa sổ mới mở Split Column by Number of Characters (Chia Cột theo Số ký tự), bạn sẽ tìm thấy ba tùy chọn. Các Tùy chọn này chỉ định tần suất và hướng tách (từ trái / phải). Nhấp vào tùy chọn đầu tiên - Once, as Far Left as Possible
Nhấp vào OK

Bước 6:

Tại đây, bạn sẽ thấy hai cột Thuộc tính - "Thuộc tính1 (năm)" và "Thuộc tính2 (loại thanh toán)". Bây giờ bạn cần phải Pivot các cột trong bảng Dữ liệu của mình. Hãy thử các bước dưới đây để áp dụng Pivot:

Chọn cột "Thuộc tính2" để Pivot dữ liệu Nhiều Cột
Đi tới tab Transform trong cửa sổ Power Query Editor
Nhấp vào tùy chọn Pivot Columns (Chuyển đổi các cột)

Cửa sổ Pivot Column sẽ được mở. Chọn cột "Value" (Giá trị) từ danh sách thả xuống.
Nhấp vào Advanced options (Tuỳ chọn Nâng cao) để chọn Aggregate Value Function (Chức năng Giá trị) trong danh sách thả xuống như được liệt kê bên dưới:

Đếm (Tất cả)
Đếm (Không trống)
Tối thiểu
Tối đa
Trung vị
Trung bình
Tổng
Không tổng hợp

Chọn Tùy chọn Sum (Tổng) trong danh sách thả xuống để tổng hợp dữ liệu số.

Nhấp vào OK.

Bước 7:

Sau khi Pivot dữ liệu, hãy nhấp đúp và đổi tên các tiêu đề cột theo yêu cầu. Ở đây, chúng tôi đang Đổi tên cột "Thuộc tính1" thành "Năm".

Bạn cũng có thể đổi tên tiêu đề cột bằng cách nhấp chuột phải vào cột "Thuộc tính1" và chọn Rename (Đổi tên). Thao tác này sẽ cho phép bạn thay đổi Tiêu đề cột đã chọn. Bây giờ, hãy chuyển đến tab Trang chủ và nhấp vào nút Close & Apply (Đóng và áp dụng) để lưu dữ liệu.

Bằng cách này, bạn có thể dễ dàng áp dụng Power BI Unpivot Columns cho các kiểu dữ liệu nhiều cột. Hình ảnh trên cho bạn thấy quy trình từng bước để chuyển đổi kiểu dữ liệu Multi-Column trong dữ liệu Microsoft Power BI Unpivot.

Trường hợp 3: Unpivot cột với tiêu đề Hàng kép

Hãy xem xét rằng chúng ta có một bảng dữ liệu như hình dưới đây:

Dữ liệu là một crosstab.
Cột có hai tiêu đề tức là tiêu đề chính (năm) và tiêu đề phụ (loại thanh toán).

Trong trường hợp này, chúng ta sẽ tìm hiểu cách sử dụng Power BI Unpivot Columns khi tiêu đề cột được chia thành hai hàng.

Các bước để Unpivot cột với tiêu đề hai hàng:

Bước 1:

Nhập dữ liệu trong Power BI

Để nhập, trước tiên hãy mở màn hình Power BI và nhấp vào tab Home
Sau đó, nhấp vào Get Data (Lấy dữ liệu). Thao tác này sẽ hiển thị danh sách Thả xuống để chọn nguồn Cơ sở dữ liệu
Nhấp vào bất kỳ nguồn dữ liệu nào mà bạn muốn trích xuất dữ liệu. Ở đây chúng tôi đang chọn tùy chọn Excel như được hiển thị trong hình ảnh bên dưới.

Bước 2:

Tải dữ liệu trong Power BI Query Editor

Đi tới tab Home
Nhấp vào tùy chọn Edit Queries (Chỉnh sửa Truy vấn)
Tải dữ liệu trong Power Query Editor

Bước 3:

Bây giờ, hãy Chuyển đổi toàn bộ bảng dữ liệu của bạn thành các hàng. Để Chuyển đổi dữ liệu:

Đi tới tab Transform trong Power Query Editor
Nhấp vào nút Transpose. Dữ liệu của bạn sẽ tự động được lật.

Bước 4:

Có một số giá trị null trong bảng dữ liệu của bạn. Để loại bỏ các giá trị null, bạn cần điền dữ liệu vào bảng dữ liệu của mình. Điều này được thực hiện bằng cách sử dụng tùy chọn Fill Down

Chọn "Column1" và chuyển đến tab Transform
Nhấp vào menu thả xuống của Fill Down
Thao tác này sẽ hiển thị các tùy chọn Down (Xuống) và Up (Lên) trong danh sách thả xuống. Chọn tùy chọn Xuống. Điều này sẽ lặp lại giá trị trên mỗi hàng trống bên dưới.

Bước 5:

Chọn "Column1" và "Column2" và hợp nhất cột

Chọn "Column1" và "Column2" để hợp nhất
Đi tới Chuyển đổi
Nhấp vào Merge Columns

Khi bạn nhấp vào tùy chọn Merge Column, một cửa sổ mới của Merge Columns sẽ bật lên. Trong cửa sổ này, hãy nhấp vào danh sách thả xuống để có tùy chọn thêm dấu phân cách giữa các giá trị của Column1 và Column2
Bây giờ, hãy chọn tùy chọn Space (Dấu cách) để thêm khoảng cách giữa hai giá trị
Sau khi chọn Dấu phân tách, Nhấp vào OK

Bước 6:

Sử dụng nút Transpose để xoay tất cả các cột từ bảng Dữ liệu trở lại dạng ban đầu

Đi tới Transform
Nhấp vào nút Transpose để xoay bảng dữ liệu

Bước 7:

Sau khi Transpose (Chuyển vị trí), hàng đầu tiên của tất cả các cột chứa dữ liệu mà chúng ta cần đặt trong các tiêu đề Cột. Bạn có thể dễ dàng thực hiện điều này bằng cách sử dụng tùy chọn Sử dụng hàng đầu tiên làm tiêu đề.

Chuyển đến tab Transform
Nhấp vào tùy chọn Use First Row as Headers (Sử dụng hàng đầu tiên làm tiêu đề).
Nó chứa hai tùy chọn trong danh sách thả xuống. Nhấp vào tùy chọn đầu tiên, tức là Use First Row as Headers. Điều này sẽ đặt hàng dưới dạng tiêu đề và do đó, tên của tiêu đề cột ban đầu sẽ được khôi phục tự động

Bước 8:

Bạn sẽ nhận được dữ liệu như đã thảo luận trong Trường hợp 2. Lặp lại tất cả các bước bạn đã học trong trường hợp 2. Hình ảnh dưới đây cho thấy các bước để có kết quả cuối cùng.

Chọn các cột để Unpivot dữ liệu tiêu đề hai hàng
Chuyển đến tab Transform
Nhấp vào danh sách thả xuống của tuỳ chọn Unpivot Columns
Chọn tùy chọn Unpivot Only Selected Columns (Chỉ Unpivot các cột đã chọn)

Bước 9:

Phân tách thuộc tính theo số lượng ký tự

Đi tới tab Home
Chọn tùy chọn Split Column (Tách cột) tại đây
Nhấp vào nút thả xuống để chọn tuỳ chọn By Number of Characters (Theo số ký tự)

Một cửa sổ mới mở ra sau khi nhấp vào tùy chọn Theo số ký tự. Đặt số ký tự tại đây.
Trong cửa sổ mới mở Split Column by Number of Characters (Chia Cột theo Số ký tự), bạn sẽ tìm thấy ba tùy chọn. Nhấp vào tùy chọn đầu tiên - Once, as Far Left as Possible và sau đó.
Nhấp vào OK

Bước 10:

Pivot các cột "Attribute2" và "Value"

Chọn các cột muốn Xoay vòng Dữ liệu Tiêu đề Hai Hàng
Nhấp vào tab Transform
Chọn tuỳ chọn Pivot Column

Cửa sổ Pivot Column sẽ được mở. Chọn cột "Value" (Giá trị) từ danh sách thả xuống.
Nhấp vào Advanced options (Tuỳ chọn Nâng cao), chọn Tùy chọn Sum (Tổng) trong danh sách thả xuống để tổng hợp dữ liệu số.
Nhấp vào OK.

Bước 11:

Đổi tên các cột

Sau khi Xoay vòng dữ liệu, hãy nhấp đúp và đổi tên các tiêu đề cột theo yêu cầu. Ở đây, chúng tôi đang Đổi tên cột "Attribute1" thành "Year". Bạn cũng có thể đổi tên tiêu đề cột bằng cách nhấp chuột phải vào cột "Attribute1" và chọn Rename. Thao tác này sẽ cho phép bạn thay đổi Tiêu đề cột đã chọn.

Bây giờ, hãy chuyển đến tab Trang chủ và nhấp vào nút Close and Apply để lưu dữ liệu.

Đây là cách bạn chuyển đổi Tiêu đề hàng kép thành Tiêu đề cột thông thường và dễ dàng tách dữ liệu.

Trường hợp 4: Pivot và Unpivot để xóa tiêu đề nhiều hàng

Bây giờ, hãy xem chúng ta có một bảng dữ liệu như hình dưới đây.

Dữ liệu là một crosstab.
Ngoài ra, nó có hai tiêu đề tức là tiêu đề chính Ngày và tiêu đề phụ Hạng mục thanh toán.
Hạng mục Thanh toán ở đây, bao gồm hai tiêu đề riêng biệt là Lương và Khuyến khích.

Trong trường hợp này, chúng ta sẽ tìm hiểu về 'cách sử dụng Power BI Unpivot Columns khi tiêu đề cột được chia thành nhiều hàng và cột'.

Các bước để mở rộng tiêu đề nhiều hàng:

Bước 1:

Nhập dữ liệu trong Power BI

Để nhập, trước tiên hãy mở Power BI desktop và nhấp vào tab Home
Sau đó, nhấp vào Get Data (Lấy dữ liệu). Thao tác này sẽ hiển thị danh sách Thả xuống để chọn nguồn Cơ sở dữ liệu
Nhấp vào bất kỳ nguồn dữ liệu nào mà bạn muốn trích xuất dữ liệu. Ở đây chúng tôi đang chọn tùy chọn Excel như được hiển thị trong hình ảnh bên dưới:

Bước 2:

Tải dữ liệu trong Power Query Editor

Đi tới tab Home
Nhấp vào tùy chọn Edit Queries (Chỉnh sửa Truy vấn)
Tải dữ liệu trong Power Query Editor

Bước 3:

Như bạn có thể thấy, dữ liệu được nhập khá lộn xộn và không gọn gàng, tức là Row1 = "Date", Row2 = "Payment Category", Row3 = "Name". Chuyển đổi dữ liệu như được đưa ra dưới đây để làm cho nó thành một thứ tự thích hợp.

Đi tới tab Transform
Nhấp vào Transpose

Lưu ý: Không cần phải chọn các cột hoặc hàng cụ thể trước khi thực hiện Chuyển đổi. Thao tác này sẽ hoán đổi tất cả các hàng và cột trong bảng Dữ liệu.

Bước 4:

Fill Down dữ liệu để loại bỏ các giá trị null

Điền dữ liệu bằng cách nhấp chuột phải vào Tiêu đề của Column1 để chọn tùy chọn Fill Down. Điều này sẽ thay thế tất cả các Giá trị null bằng các Giá trị trong "Column1"
Nhấp vào tùy chọn Down để Fill Down dữ liệu. Tương tự, hãy sử dụng Tùy chọn Fill Down cho tất cả các cột ở bất kỳ nơi nào có giá trị null. Bạn cũng có thể sử dụng điều này bất cứ khi nào bạn muốn các giá trị trên được lặp lại bên dưới

Bước 5:

Nhấp chuột phải vào "Column3" và nhấp vào Tuỳ chọn Xoá. Thao tác này sẽ xóa Cột 3 chứa giá trị Null.

Bước 6:

Sử dụng lại chức năng "Fill Down" cho Column4 và Column5

Chọn "Column4" và "Column5" và chuyển đến tab Chuyển đổi
Chọn tùy chọn Fill Down
Nhấp vào tùy chọn Down từ nút Fill Down

Bước 7:

Như bạn có thể thấy, hàng đầu tiên không có bất kỳ giá trị thích hợp nào. Do đó nó có thể được loại bỏ.

Đi tới tab Home
Nhấp vào nút Remove Rows (Xóa hàng)
Trong danh sách thả xuống của tùy chọn Xóa hàng, nhấp vào tùy chọn Remove Top Rows (Xóa hàng trên cùng). Khi đó, một cửa sổ được mở. Trong cửa sổ mới này, hãy chèn các giá trị số để bạn có thể xóa hoặc loại bỏ nhiều số hàng đó khỏi đầu bảng Dữ liệu. Ở đây, chúng tôi đang đặt 1 làm đầu vào để hàng đầu tiên chứa giá trị Null có thể bị xóa.

Bước 8:

Bảng đã trở nên ổn hơn rồi. Bây giờ chúng ta sẽ đi tới khôi phục tiêu đề Cột bằng cách nhấp vào tùy chọn sử dụng hàng đầu tiên làm tiêu đề. Nó sẽ đặt các giá trị từ hàng đầu tiên của mỗi cột đến tiêu đề cột.

Đi tới tab Transform
Nhấp vào tùy chọn Use First Row as Headers (Sử dụng hàng đầu tiên làm tiêu đề)
Chọn tùy chọn Use First Row as Headers từ danh sách thả xuống

Bước 9:

Bây giờ, chúng tôi đã sẵn sàng áp dụng Unpivot trên bảng Dữ liệu

Chọn các Cột Giá trị ("AbduSalaam" và "Jeffery") để Unpivot Multi-Row Header Data
Chuyển đến tab Transform
Nhấp vào danh sách thả xuống Unpivot Columns
Chọn Unpivot Only Selected Columns (Chỉ Unpivot các cột đã chọn). Thao tác này sẽ Unpivot tất cả các cột có chứa các Giá trị này