Trước đây tôi cũng giống như bạn, đó là chẳng cần biết Power Query là gì. Tôi cứ sử dụng Excel một cách tự nhiên, vô thức. Có những lúc công việc trở nên rất phức tạp: Khi phải làm sạch 1 bảng dữ liệu từ người khác gửi (hay trích xuất từ phần mềm ra, hoặc tải từ trên mạng về), khi thì phải gom dữ liệu từ nhiều file Excel vào chung 1 file… Những việc đó khiến tôi mất nhiều thời gian. Muốn tự động những việc này, tôi có tìm hiểu cả VBA. Nhưng để dùng được VBA làm những việc này thì cũng mất khá nhiều thời gian. Tôi tự hỏi: có cách nào giúp làm những việc này trở nên dễ dàng hơn không?

Hóa ra giải pháp đã có sẵn rồi, chỉ là tôi không chú ý tới nó. Giải pháp chính là Power Query.

Power Query là gì?

Power Query là công cụ giúp tạo ra 1 quy trình tự động cho việc tổ chức, xử lý, tái cấu trúc lại bảng dữ liệu.

Trong các phiên bản Office 2013 trở về trước (2010, 2013), bạn phải cài đặt Power Query dưới dạng 1 Add-in (tiện ích mở rộng).

Từ phiên bản Office 2016 trở về sau (2016, 2019, 365), Power Query đã được tích hợp sẵn trong Excel: Nhóm Get & Transform Data trong thẻ Data

Giao diện Get & Transform Data trong thẻ Data

Tại sao cần học Power Query?

Khi được hỏi “điều gì là quan trọng nhất” khi làm việc trên Excel, tôi sẽ nói ngay rằng: Cần chắc chắn rằng dữ liệu đã được làm sạch và thống nhất. Điều này bao gồm:

• Cách cấu trúc bảng dữ liệu đúng. Mỗi khi bế tắc trong việc tính toán với dữ liệu, tôi đều nghĩ tới việc “cấu trúc lại”. Khi cấu trúc lại thì hóa ra việc tính toán lại trở nên vô cùng đơn giản.
• Các giá trị được nhập vào là đúng về loại dữ liệu, về nội dung và được cập nhật liên tục
• Các bảng dữ liệu cùng 1 chức năng cần được tập hợp thống nhất trong 1 bảng. Việc này cũng cần được làm 1 cách tự động.

Cách cấu trúc bảng dữ liệu đúng

Việc cấu trúc bảng dữ liệu đúng cách có thể giúp ích rất nhiều:

• Có thể sử dụng hàm SUMIFS, COUNTIFS để tính toán, báo cáo theo nhiều điều kiện một cách dễ dàng
• Có thể trích lọc dữ liệu chi tiết bằng Advanced Filter một cách dễ dàng
• Sử dụng được Pivot Table để làm các báo cáo động, báo cáo phân tích dữ liệu…

Hình dưới đây sẽ mô tả thế nào là 1 bảng dữ liệu được cấu trúc đúng:

Nhưng không phải lúc nào chúng ta cũng làm việc ngay với cấu trúc đúng. Bởi việc nhập dữ liệu trong cấu trúc đúng mất rất nhiều thời gian. Bạn phải nhập đủ nội dung trên tất cả các cột, trong khi với cấu trúc bên phải, chỉ cần nhập mỗi giá trị tại vị trí giao điểm của các đối tượng đã có sẵn. Đó là lý do tại sao người ta lại hay chọn cách nhập dữ liệu trên 1 cấu trúc không đúng chuẩn.

Điều này dẫn tới 1 khó khăn là: phải cấu trúc lại thì mới tính ra kết quả như ý muốn được.

Thử hình dung bạn phải tổ chức lại 1 cách thủ công, copy paste từng phần, rồi phải xoay chuyển cách biểu diễn các nội dung… quả là 1 công việc khó khăn và tốn thời gian.

Power Query giúp bạn dễ dàng cấu trúc lại bảng dữ liệu. Đặc biệt hơn nữa là những gì bạn làm trong Power Query đều được ghi lại, giúp cho việc cập nhật thêm thông tin trong bảng dữ liệu gốc sẽ được tự động cập nhật vào bảng kết quả đã tái cấu trúc.

Tham khảo:

Hướng dẫn sử dụng Power Query để chuẩn hoá dữ liệu

Các giá trị được nhập vào là đúng về loại dữ liệu

Khi lấy dữ liệu từ các nguồn không phải từ Excel: Web, SQL, các phần mềm khác… thì chúng ta không để chắc chắn được về loại dữ liệu nhận được. Các dữ liệu dạng Number, Date thường bị nhầm lẫn sang dạng Text, khiến cho việc tính toán sẽ không ra kết quả đúng.

Thông thường bạn sẽ phải sửa lại dữ liệu, định dạng lại. Nhưng mỗi khi muốn cập nhật thêm dữ liệu, bạn sẽ phải thực hiện lại các thao tác đó.

Power Query có thể giúp bạn quy định lại dữ liệu có trong từng cột về đúng loại dữ liệu. Bạn sẽ không còn mất thời gian lo lắng cho việc này nữa. Nạp dữ liệu thô vào, chỉ cần bấm Refresh tại bảng kết quả, bạn đã có được bảng dữ liệu chính xác để làm việc rồi, hơn nữa quá trình này lại được thực hiện 1 cách tự động và rất nhanh.

Tham khảo:

Lấy dữ liệu tài chính tự động từ Web vào Excel bằng Power Query

Lấy dữ liệu trên trang Facebook cá nhân vào Excel bằng Power Query

Tập hợp dữ liệu từ nhiều Sheet, nhiều Workbook vào 1 bảng chung

Tôi đã gặp rất nhiều trường hợp này, đó là khi bạn phải tập hợp dữ liệu từ nhiều chi nhánh, mỗi chi nhánh 1 file Excel giống nhau, hoặc nhiều người làm chung trên 1 file excel, mỗi người 1 Sheet (hay mỗi ngày 1 Sheet). Để báo cáo được, bạn cần phải tập hợp tất cả các dữ liệu này trên 1 bảng chung. Vấn đề là các Sheet, các Workbook sẽ phát sinh thêm thường xuyên. Tập hợp lại đã khó, đảm bảo dữ liệu mới không bị thiếu, sót khi tập hợp còn khó hơn.

Nếu không biết Power Query, bạn sẽ cần dùng tới VBA (gần như chắc chắn). Nhưng VBA trong trường hợp này cũng không hề đơn giản. Những code cần sử dụng trong trường hợp này thường là khó, phức tạp, không dễ để học và làm được trong 1 thời gian ngắn.

Khi biết tới Power Query, tôi đã thực sự ngạc nhiên vì nó có thể giải quyết vấn đề này 1 cách triệt để. Sẽ có 1 chút câu lệnh, nhưng đều rất đơn giản, dễ học, dễ thao tác. Kết quả lại rất bất ngờ: chỉ làm 1 lần, sau này khi thêm Sheet, thêm Workbook thì vẫn tự động cập nhật vào bảng chung. Điều này thật ngoài sức tưởng tượng.

Tham khảo:

Cách gộp dữ liệu từ nhiều bảng vào 1 bảng sử dụng Power Query

Power Query dễ học và dễ sử dụng

Nếu dành chút thời gian đọc những bài tham khảo được gợi ý ở trên, bạn có thể thấy Power Query rất dễ làm. Bạn sẽ có thể dễ dàng thực hiện theo từng bước. Các bước đó sẽ được ghi lại tự động để bạn không phải làm lại các thao tác đó nữa. Vậy nên việc của bạn chỉ đơn giản là: Học thuộc lòng các thao tác đó.

Tất nhiên sẽ có những trường hợp khó, đòi hỏi phải kỳ công trong việc xử lý. Nhưng tin tôi đi, độ khó khi làm bằng Power Query đã giảm đi rất nhiều lần so với làm bằng VBA.

Để giúp các bạn có cái nhìn rõ hơn về tính ứng dụng của Power Query trong công việc, tôi sẽ xây dựng một số mô hình phân tích dữ liệu để làm rõ hơn nội dung này.

Đón xem bài viết: Hướng dẫn cách xây dựng bảng dữ liệu phân tích hiệu suất lao động bằng Power Query