Hướng dẫn kỹ thuật xoay bảng trong Power Query dễ hiểu nhất

Trong việc tổ chức dữ liệu, một lỗi phổ biến thường gặp là bảng dữ liệu có chiều ngang, hay bảng dữ liệu hai chiều. Tại sao cách cấu trúc bảng dữ liệu như vậy lại không phù hợp? Làm thế nào để xoay bảng về dạng bảng chiều dọc tiêu chuẩn? Hãy cùng Gitiho tìm hiểu trong bài viết này nhé.

Trước hết, các bạn hãy tải file tài liệu mà chúng mình để trong mục "Tài liệu đính kèm" của bài viết về nhé. Chúng ta sẽ cùng thực hành ngay trên file đó để các bạn nắm rõ các thao tác xoay bảng trong Power Query.

Nguyên nhân Power Query cấu trúc bảng dữ liệu chiều ngang

Bảng dữ liệu chiều ngang thường có dạng như sau:

Trong đó:

• Cột chỉ tiêu: mỗi chỉ tiêu tương ứng với 1 dòng. Khi có thêm chỉ tiêu thì bảng sẽ tăng thêm dòng (chiều dọc)
• Các cột tháng: mỗi cột tương ứng với 1 tháng, các tháng sẽ theo 1 năm. Khi thêm dữ liệu năm khác, thêm tháng khác thì bảng sẽ tăng thêm về cột (chiều ngang)

Như vậy bảng dữ liệu có thể tăng theo 2 chiều. Số liệu khi phát sinh sẽ nhập tại vị trí giao điểm giữa dòng chỉ tiêu và cột tháng. Điều này giúp bạn nhập dữ liệu nhanh hơn.

Dạng bảng này có nhược điểm là:

1. Khó tính toán bằng các hàm, công thức do đặc trưng của việc tính toán trong dạng Table là tính toán theo cả cột (theo field - trường dữ liệu) => bị hạn chế về tính toán
2. Khó quản lý và giới hạn bảng: do thêm cột theo chiều ngang sẽ làm tăng thêm các Fields => dễ bị bỏ sót dữ liệu khi tính toán, báo cáo
3. Dòng tiêu đề chứa thông tin quan trọng (tháng, năm). Thông tin này thường là yếu tố quan trọng được sử dụng trong mô hình dữ liệu, trong các hàm phân tích dữ liệu theo thời gian (Time Intelligence) => Khi thông tin nằm ở dòng tiêu đề sẽ không sử dụng được. Cần đưa thông tin này xuống thành các dòng nội dung theo các cột Tháng, Năm riêng biệt.

Tuy nhiên trong thực tế chúng ta lại hay gặp cách cấu trúc bảng theo chiều ngang như vậy. Bởi một số nguyên nhân như:

• Thói quen tổ chức dạng bảng ngang để tận dụng ưu điểm về nhập dữ liệu nhanh, đơn giản.
• Bảng dữ liệu được trích xuất từ phần mềm hoặc trang web (hoặc do người khác gửi) đã có sẵn cách tổ chức như vậy, không tự ý thay đổi được.

Do đó chúng ta cần tới 1 công cụ giúp tự động chuyển đổi từ dạng bảng ngang thành dạng bảng dọc theo đúng tiêu chuẩn về cấu trúc bảng dữ liệu.

4 bước xoay cấu trúc bảng trong Power Query

Bước 1: Nạp bảng dữ liệu vào Power Query trong Excel

Việc này khá đơn giản, bạn chỉ cần:

• Đặt bảng dữ liệu ở dạng Table (tại thẻ Insert chọn mục Table)
• Bấm vào một vị trí bất kỳ trong bảng, tại thẻ Data chọn chức năng From Table/Range 

Khi đó Power Query sẽ tự nhận dạng bảng, kết quả thu được như sau:

Bước 2: Đưa nội dung dòng tiêu đề xuống phần nội dung của bảng

Vì nội dung Tháng, Năm đang ở vị trí dòng tiêu đề nên không xoay bảng ngay được, sẽ bị mất phần nội dung này => cần đưa dòng tiêu đề xuống phần nội dung trước. Để làm điều này bạn có thể sử dụng chức năng Use Header as First Row (bấm vào Use First Row as Headers rồi chọn mục Use Header as First Row)

Kết quả của thao tác này là:

Bước 3: Xoay bảng với chức năng Transpose

Tại thẻ Transform của Power Query Editor, bạn bấm chọn chức năng Transpose để xoay bảng dữ liệu theo chiều dọc. Nguyên tắc xoay bảng được hiểu như sau:

• Mỗi dòng của bảng dữ liệu ban đầu sẽ được xoay lại thành 1 cột.
• Các dòng tiếp theo sẽ xoay thành các cột nối tiếp nhau trong bảng kết quả.

Bước 4: Hoàn thiện bảng kết quả

Cuối cùng bạn chỉ cần thực hiện một vài thao tác hoàn thiện bảng như đặt lại tên tiêu đề cho các cột, xác định kiểu dữ liệu trong từng cột.

Sau đó là đóng bảng và chọn phương pháp kết xuất bảng tùy theo mục đích sử dụng. Nếu bạn muốn thực hành theo hướng dẫn trong bài viết này, bạn có thể tải file tài liệu kèm theo trong bài viết để làm thử nhé.

Kết luận

Như vậy chỉ với 4 bước đơn giản bạn đã có thể thực hiện xoay bảng dữ liệu về chiều dọc một cách dễ dàng và hoàn toàn tự động. Hãy thực hành ngay để ghi nhớ các bước làm cho lần sau nhé. Nếu các bạn gặp khó khăn khi thực hành thì hãy để lại bình luận để được giải đáp nha.

Để sử dụng thành thạo Power Query, các bạn hãy tham gia vào khóa học 2 trong 1 dưới đây:

Power Pivot, Power Query - Biến Excel thành công cụ Phân tích dữ liệu chuyên sâu

Không chỉ được học kiến thức từ cơ bản đến nâng cao về Power Query mà bạn còn được học cả về Power Pivot để trở thành chuyên gia Excel trong phân tích dữ liệu. Khóa học rất phù hợp với các bạn đang làm việc trong lĩnh vực Kế toán, Tài chính, Quản lý, Sale, Ngân hàng, Marketing, Quản lý dự án,... 

Trong quá trình học, các bạn sẽ được chuyên gia hỗ trợ 24/7, giải đáp nhanh mọi thắc mắc giúp bạn hiểu bài kỹ hơn. Gitiho không giới hạn thời gian hay số lượt học nên các bạn cứ thoải mái sắp xếp lịch học phù hợp nhé. Chúc các bạn học tập hiệu quả.