[Power Query Tutorial] Cách sử dụng Group by

Khi xử lý các tập dữ liệu lớn trong Excel hay Power BI, việc tóm tắt và tổng hợp thông tin theo từng nhóm là yêu cầu gần như bắt buộc. Trong số các công cụ xử lý dữ liệu hiện nay, Group by nổi lên như một trợ thủ đắc lực giúp bạn cô đọng dữ liệu thô thành các báo cáo tinh gọn chỉ trong vài cú click chuột. Bài viết này, Starttrain sẽ giúp bạn hiểu rõ bản chất Group by là gì, cách thức hoạt động của nó trong Power Query, đồng thời đi vào minh họa từng bước cực kỳ trực quan dựa trên dữ liệu thực tế.

Group by là gì?

Group by (Gom nhóm dữ liệu) là một khái niệm cơ bản bắt nguồn từ ngôn ngữ truy vấn cơ sở dữ liệu SQL. Hiểu một cách đơn giản nhất, Group by là quá trình gộp các dòng có cùng giá trị trong một hoặc nhiều cột được chỉ định lại thành một dòng duy nhất, đồng thời thực hiện các phép toán tính toán (như tính tổng, trung bình, đếm dòng, tìm giá trị lớn nhất/nhỏ nhất) trên các cột còn lại.

Ví dụ, nếu bạn có danh sách 1.000 giao dịch bán hàng của nhiều thành phố khác nhau, việc áp dụng Group by theo “Thành phố” với phép toán “Tính tổng doanh thu” sẽ trả về một bảng dữ liệu mới cực kỳ gọn gàng: mỗi dòng đại diện cho một thành phố duy nhất đi kèm với tổng số tiền bán được của thành phố đó.

Chức năng Group by trong Power Query

Về Group by trong Power Query

Trong bộ công cụ Power Query, Group by không chỉ đơn thuần là một nút bấm gộp dòng, mà là một trong những tính năng biến đổi dữ liệu cốt lõi và mạnh mẽ nhất. Thay vì phải vật lộn với các công thức mảng Excel phức tạp hay viết mã DAX cồng kềnh sau khi tải dữ liệu, Power Query cho phép bạn thực hiện chuẩn hóa và thu gọn dữ liệu ngay từ khi đi vào.

Bạn có thể tìm thấy nút Group By ở hai nơi trên thanh Ribbon của Power Query Editor:

• Thẻ Home (Home tab): Nằm trong nhóm Transform.
• Thẻ Transform (Transform tab): Nằm trong nhóm Table.

Cả hai vị trí này đều dẫn đến cùng một hộp thoại thiết lập, giúp bạn dễ dàng thao tác dù đang làm việc ở bất kỳ giao diện nào.

Cơ chế hoạt động

Khi bạn thực hiện thao tác Group by, Power Query sẽ sử dụng hàm Table.Group trong ngôn ngữ M. Hàm này sẽ quét qua toàn bộ bảng dữ liệu, phân loại các bản ghi dựa trên cột khóa mà bạn đã chọn, sau đó tạo ra một bảng mới với các giá trị duy nhất từ cột khóa đó, đi kèm với các cột kết quả tính toán mới.

Các phép toán tổng hợp hỗ trợ

Hộp thoại Group By của Power Query cung cấp sẵn một loạt các phép toán thông dụng giúp bạn xử lý hầu hết mọi yêu cầu nghiệp vụ từ cơ bản đến nâng cao:

• Sum: Tính tổng giá trị số của nhóm.
• Average: Tính giá trị trung bình cộng.
• Median: Tìm giá trị trung vị (giá trị nằm ở chính giữa chuỗi số).
• Min / Max: Tìm giá trị nhỏ nhất hoặc lớn nhất trong nhóm.
• Count Rows: Đếm tổng số dòng thuộc nhóm đó (rất hữu ích để thống kê tần suất).
• Count Distinct Rows: Đếm số lượng giá trị duy nhất (không trùng lặp) trong nhóm.
• All Rows: Phép toán đặc biệt giúp giữ lại toàn bộ các dòng dữ liệu chi tiết dưới dạng các bảng con lồng ghép (Nested Tables). Đây là tính năng nâng cao cực kỳ mạnh mẽ giúp bạn vừa có thể xem dữ liệu tổng hợp, vừa dễ dàng bung (Expand) ngược lại dữ liệu chi tiết khi cần thiết.

Lợi ích khi sử dụng Group by trong Power Query

• Tối ưu hóa hiệu năng hệ thống (Data Reduction): Việc nạp hàng triệu dòng dữ liệu thô vào Data Model của Power BI hay một bảng tính Excel sẽ khiến file trở nên cực kỳ nặng nề, tốn RAM và làm chậm các công thức tính toán sau đó. Bằng cách Group by ngay trong Power Query, bạn có thể rút gọn một bảng dữ liệu từ 10 triệu dòng xuống còn vài chục ngàn dòng đã được tổng hợp, giúp mô hình dữ liệu của bạn hoạt động mượt mà, phản hồi báo cáo tức thì.
• Linh hoạt tối đa với hai chế độ Basic & Advanced:
  • Chế độ Basic phù hợp cho các tác vụ nhanh chóng khi bạn chỉ cần gom nhóm theo 1 cột điều kiện duy nhất.
  • Chế độ Advanced mở ra khả năng tùy biến vô hạn: gom nhóm theo nhiều cấp độ phân loại (ví dụ: Gom theo Năm -> Tháng -> Thành phố), đồng thời tính toán cùng lúc nhiều chỉ số khác nhau (ví dụ: vừa tính tổng doanh thu, vừa tính số đơn hàng trung bình, vừa đếm số khách hàng duy nhất).
• Tự động hóa hoàn toàn và Dễ dàng chỉnh sửa (Applied Steps): Mọi thao tác Group by của bạn đều được ghi lại thành một bước (Step) trong bảng điều khiển Applied Steps. Nếu sau này dữ liệu nguồn có thêm các dòng mới, bạn chỉ cần nhấn Refresh, Power Query sẽ tự động chạy lại toàn bộ quy trình gom nhóm này mà bạn không cần làm lại từ đầu. Hơn nữa, bạn có thể dễ dàng sửa đổi cấu trúc gom nhóm bất cứ lúc nào bằng cách click vào biểu tượng bánh răng cưa bên cạnh bước “Grouped Rows”.

Minh họa cụ thể chức năng Group by trong Power Query

Bước 1: Xác định bảng dữ liệu và nút chức năng

Trong giao diện Power Query Editor, ta có bảng dữ liệu FactTransactions chứa các thông tin như: card_id, client_id, amount (số tiền), date (ngày giao dịch), errors, mcc, và merchant_city (thành phố của đơn vị bán hàng).Để bắt đầu gom nhóm, bạn chọn thẻ Transform trên thanh công cụ và nhấn chọn biểu tượng Group By ở góc trái màn hình.

Bước 2: Thiết lập gom nhóm cơ bản

Khi hộp thoại Group By hiện lên, bạn chọn chế độ Basic mặc định để thực hiện gom nhóm theo một điều kiện duy nhất:

• Cột dùng để gom nhóm (dropdown phía trên): Chọn merchant_city (Thành phố).
• New column name (Tên cột mới): Nhập tên cột chứa kết quả tính toán là Total Amount.
• Operation (Phép toán): Chọn Sum (Tính tổng).
• Column (Cột tính toán): Chọn cột chứa giá trị số tiền là amount.

Sau khi thiết lập xong, bạn nhấn OK.

Bước 3: Kết quả của phép gom nhóm cơ bản

Ngay lập tức, Power Query sẽ thu gọn bảng dữ liệu của bạn. Thay vì hàng ngàn giao dịch riêng lẻ, bảng dữ liệu lúc này chỉ còn đúng 2 cột: merchant_city và Total Amount.Mỗi thành phố (như Mamaroneck, Mount Vernon, ONLINE,…) bây giờ chỉ xuất hiện đúng 1 dòng đi kèm tổng số tiền giao dịch tương ứng.

Bước 4: Thiết lập gom nhóm theo nhiều tiêu chí

Trong nhiều trường hợp, việc gom nhóm theo 1 cột là chưa đủ. Bạn muốn biết tổng số tiền giao dịch của từng thành phố đối với từng thẻ (card_id) cụ thể.

Lúc này, bạn bấm đúp vào bước “Grouped Rows” ở phần Applied Steps để mở lại hộp thoại, sau đó chọn chế độ Advanced:

• Nhấp vào nút Add grouping để thêm một tiêu chí phân loại thứ hai bên dưới cột merchant_city và chọn cột card_id.
• Bên dưới phần tính toán, bạn giữ nguyên thiết lập tính tổng cột amount thành cột mới là Total Amount.

Bước 5: Kết quả gom nhóm đa tầng

Kết quả trả về là một bảng dữ liệu chi tiết hơn. Bạn sẽ thấy cột merchant_city kết hợp với cột card_id để tạo ra các tổ hợp duy nhất. Ví dụ, thành phố Mamaroneck sẽ được chia nhỏ ra thành các dòng tương ứng với các thẻ số 19, thẻ số 27, … đi kèm với tổng số tiền giao dịch của riêng thẻ đó tại thành phố đó.

Bước 6: Gom nhóm kết hợp đồng thời nhiều phép toán tính toán

Power Query còn cho phép bạn tính toán nhiều chỉ số khác nhau trong cùng một lần gom nhóm. Tại hộp thoại Advanced, bên dưới phần cấu hình tính tổng:

• Bạn nhấp vào nút Add aggregation.
• Đặt tên cột mới là Avg Amount (Số tiền trung bình).
• Chọn Operation là Average (Tính trung bình).
• Chọn Column tính toán là cột amount.

Nhấn OK để áp dụng thay đổi.

Bước 7: Bảng tổng hợp hoàn chỉnh đa chỉ số

Bảng dữ liệu cuối cùng sẽ hiển thị đầy đủ 4 cột gồm: merchant_city, card_id, Total Amount (Tổng số tiền) và Avg Amount (Số tiền trung bình trên mỗi giao dịch).

Cấu trúc mã M của hàm Table.Group

Khi bạn thực hiện thao tác Group by trên giao diện, Power Query sẽ tự động viết một dòng mã bằng ngôn ngữ M ở thanh công thức. Hiểu rõ cấu trúc của đoạn mã này sẽ giúp bạn dễ dàng tùy biến dữ liệu theo những cách mà giao diện kéo thả thông thường không thể làm được.Cú pháp tổng quát của hàm gom nhóm trong ngôn ngữ M là: Table.Group(table, key, aggregatedColumns)

Trong đó:

• table: Bảng dữ liệu gốc cần xử lý (ví dụ: #”Changed Type”).
• key: Cột hoặc danh sách các cột khóa được sử dụng để gom nhóm, đặt trong dấu ngoặc nhọn {} (ví dụ: {“merchant_city”} hoặc {“merchant_city”, “card_id”}).
• aggregatedColumns: Danh sách các cột tính toán mới được tạo ra. Phần này được định nghĩa dưới dạng một danh sách các danh sách con (list of lists), có cấu trúc như sau: {“Tên cột mới”, each Phép_toán([Cột_gốc]), kiểu_dữ_liệu}

Phân tích một ví dụ thực tế:Hãy nhìn lại đoạn mã M-code ở Bước 7:

 
= Table.Group(

#"Changed Type",

{"merchant_city", "card_id"},

{

{"Total Amount", each List.Sum([amount]), type nullable number},

{"Avg Amount", each List.Average([amount]), type nullable number}

}

)

Ở đây:Ta gom nhóm bảng #”Changed Type” dựa trên 2 cột khóa là “merchant_city” và “card_id”.

Ta tạo ra 2 cột mới cùng lúc:

Cột “Total Amount” thực hiện phép toán List.Sum trên cột [amount].

Cột “Avg Amount” thực hiện phép toán List.Average trên cột [amount].

So sánh Group by trong Power Query và Pivot Table trong Excel

Rất nhiều người mới học phân tích dữ liệu thường thắc mắc: “Tại sao phải dùng Group by trong Power Query khi chúng ta có thể làm điều tương tự bằng cách kéo thả Pivot Table trong Excel?”.

Lưu ý khi sử dụng Group by

Mặc dù Group by là một công cụ cực kỳ hữu ích, bạn vẫn cần ghi nhớ một số lưu ý quan trọng để tránh làm hỏng cấu trúc dữ liệu hoặc gây lỗi hệ thống:Mất mát dữ liệu chi tiết (Data Loss): Khi bạn gom nhóm dữ liệu bán hàng theo ngày hoặc theo thành phố, các thông tin chi tiết của từng giao dịch sẽ bị biến mất hoàn toàn.

Do đó, chỉ nên sử dụng Group by khi bạn chắc chắn không cần dùng đến các chi tiết đó ở các bước phân tích sau này. Mẹo nhỏ: Nếu muốn giữ lại dữ liệu chi tiết, hãy chọn phép toán All Rows trong phần Operation để tạo ra một cột chứa các bảng con chứa đầy đủ các dòng chi tiết ban đầu.

Đồng bộ kiểu dữ liệu (Data Types): Hãy đảm bảo rằng cột mà bạn chọn thực hiện các phép toán tính tổng hoặc trung bình bắt buộc phải có kiểu dữ liệu dạng Số. Nếu cột đó chứa ký tự text hoặc giá trị lỗi, Power Query sẽ trả về kết quả lỗi trên toàn bộ nhóm đó.

Tối ưu hóa cơ chế Query Folding: Khi bạn kết nối Power Query với các cơ sở dữ liệu quan hệ (như SQL Server, MySQL), bước Group by nên được thực hiện càng sớm càng tốt. Power Query có khả năng tự động dịch bước Group by này thành câu lệnh SQL và gửi trực tiếp về máy chủ cơ sở dữ liệu xử lý. Điều này giúp máy tính cá nhân của bạn không cần phải tải hàng triệu dòng dữ liệu thô về rồi mới gom nhóm.

Tổng kết

Gom nhóm dữ liệu bằng Group by trong Power Query là kỹ năng cốt lõi giúp bạn làm sạch và cô đọng dữ liệu một cách thông minh, tối giản trước khi đưa vào phân tích hay trực quan hóa. Việc nắm chắc kỹ thuật Group by và kỹ năng đọc hiểu cấu trúc M-code sẽ giúp bạn chủ động tùy biến các phép toán phức tạp mà giao diện thông thường không đáp ứng được. Hãy bắt đầu ứng dụng tính năng Group by vào các dự án phân tích của mình ngay hôm nay để trải nghiệm sự khác biệt về cả hiệu suất hệ thống lẫn tốc độ xử lý báo cáo.

Tìm hiểu thêm khóa học Power BI tại Starttrain để xây dựng báo cáo và tư duy phân tích dữ liệu.