Trong bài viết này, chúng ta cùng tìm hiểu một ứng dụng của Power Query trong việc xây dựng mô hình dữ liệu về phân tích năng suất lao động. Mô hình này cũng thường gặp trong thực tế khi làm việc với Excel. Hãy xem với Power Query chúng ta có thể làm được gì nhé.

Trong phần 1, chúng ta đã tìm hiểu cách tái cấu trúc lại các bảng dữ liệu để có cấu trúc đúng tiêu chuẩn. Mục đích của việc này là tạo ra bảng kết quả có thể sử dụng được Pivot Table – nội dung quan trọng nhất trong việc giúp phân tích dữ liệu.

Xem lại: Ứng dụng Power Query xây dựng mô hình dữ liệu phân tích năng suất lao động – Phần 1

Các bạn có thể tải file kết quả phần 1 tại địa chỉ: http://bit.ly/31XTYVj

Phần 2: Gộp các bảng dữ liệu vào 1 bảng duy nhất

Ngoài việc cấu trúc bảng dữ liệu đúng chuẩn, chúng ta còn phải đảm bảo tất cả các dữ liệu được tập trung, thống nhất trên 1 bảng dữ liệu. Có như vậy thì chúng ta mới có đủ thông tin để phân tích.

Đặc điểm của mô hình dữ liệu trong bài tập là dữ liệu chia ra 3 bảng tương ứng với 3 năm, theo các Sheet riêng. Do đó yêu cầu công việc là chúng ta phải gộp các bảng vào thành 1 bảng duy nhất.

Trong file kết quả của Phần 1, chúng ta có được kết quả là 3 bảng: Data_2017, Data_2018, Data_2019 tương ứng với 3 Sheet.

Các bước để gộp file trong Power Query như sau:

Bước 1: Tạo 1 Query mới bằng cách: Chọn Thẻ Data > Get Data > From Other Source > Blank Query

Bước 2: Trong Query mới được tạo, nhập câu lệnh sau đây trên thanh công thức:

=Excel.CurrentWorkbook()

Chú ý: Phải viết hoa đúng vị trí các chữ cái. Khi nhập lệnh này, bạn sẽ được gợi ý. Có thể chọn theo nội dung được gợi ý để viết đúng cú pháp.

Mục đích: Để có thể lấy toàn bộ nội dung các bảng có trong Workbook đưa vào trong Power Query.

Kết quả của câu lệnh:

Trong đó chúng ta tập trung vào các bảng có tên là Data_2017, Data_2018, Data_2019 vì đây là các bảng đã được xử lý qua phần 1.

Bước 3: Lọc các bảng cần sử dụng

Tại vị trí nút lọc trong cột Name, chúng ta chỉ giữ lại tên các bảng đã qua xử lý. Các bảng không dùng đến sẽ bỏ chọn.

Đây là các bảng sẽ được dùng để gộp lại vào 1 bảng chung. Các bảng này đã được xử lý về cấu trúc, cách tổ chức giống nhau.

Bước 4: Mở rộng nội dung chi tiết trong bảng

Để xem chi tiết nội dung trong bảng theo các cột, chúng ta sẽ bỏ dấu tích trong mục Use original column name as prefix rồi chọn OK

Kết quả nhận được chính là bảng dữ liệu gộp từ 3 bảng:

Trong đó xuất hiện thêm cột Name: đây chính là cột thể hiện nội dung trong từng dòng thuộc bảng nào.

Bước 5: Tách số năm ra thành 1 cột riêng

Với các tên Data_2017, Data_2018, Data_2019 sẽ là các ký tự Text. Nếu muốn tách riêng số năm ra khỏi các cột này, chúng ta có thể làm cách sau:

Chọn Split Column > By Delemiter > Dựa trên ký tự nhận biết vị trí cần tách là dấu gạch dưới

Như vậy kết quả sẽ gồm 2 cột:

• Name.2 : Đổi tên thành ‘Năm‘
• Name.1 : Có thể xóa cột này đi (chọn cột Name.1 rồi bấm chuột phải, chọn Remove)

Bước 6: Hoàn thành, bấm chọn thẻ Home rồi bấm Close & Load để đóng Power Query và xuất bảng kết quả ra file Excel

Bước 7: Do tại bước 2, chúng ta lấy toàn bộ các bảng trong Excel.CurrentWorkbook. Do đó khi Refresh lại dữ liệu, bảng kết quả sẽ bị Duplicate (trùng lặp dữ liệu). Để ngăn việc này, chúng ta sẽ làm như sau:

• Bấm chuột phải vào vị trí bất kỳ trong bảng kết quả, chọn Table > Edit Query (hoặc nháy đúp chuột vào tên Query trong bảng Queries & Connections) để quay trở lại cửa sổ làm việc của Power Query
• Trong cửa sổ Power Query với Query1 (là Query vừa làm việc ở trên), chọn mục Source trên cửa sổ Applied Step. Sau đó chọn mục Refresh Preview > Bấm chọn Refresh Preview

Kết quả sẽ có thêm bảng Query1 (bảng kết quả ở bước 6)

Thực hiện lại thao tác lọc bảng (trong bước 3): Chỉ chọn đúng 3 bảng cần làm việc: Data_2017, Data_2018, Data_2019. Các bảng khác sẽ bỏ chọn.

Sau đó bấm Close & Load để hoàn thành công việc.

File kết quả có thể tải về tại đây:

Như vậy là chúng ta đã hoàn thành được công việc tái cấu trúc lại toàn bộ các bảng dữ liệu để cho ra 1 bảng dữ liệu duy nhất. Bảng dữ liệu này sẽ được cập nhật tự động mỗi khi file gốc có sự thay đổi nội dung. Việc này giúp quá trình nhập dữ liệu vẫn trên File gốc, bảng gốc, không cần phải làm lại thao tác tái cấu trúc dữ liệu nữa. Rất tiện lợi phải không nào.

Trong phần sau, chúng ta sẽ cùng tìm hiểu về cách sử dụng công thức tính trong Power Query để tạo ra các dữ liệu cần thiết cho báo cáo.