Power Query và Power Pivot: khác nhau và cách kết hợp

Power Query dùng để lấy, làm sạch và biến đổi dữ liệu; Power Pivot dùng để tạo mô hình và tính chỉ số bằng DAX trong Excel. Bạn có thể dùng riêng từng công cụ, nhưng khi báo cáo có nhiều bảng và cần lọc theo cửa hàng, sản phẩm, việc kết hợp cả hai giúp quy trình rõ ràng hơn.

Ví dụ dưới đây đi từ bảng bán hàng đến biên lợi nhuận theo cửa hàng. Toàn bộ số liệu là giả lập để bạn tự kiểm tra, không phải kết quả của một khách hàng.

Power Query và Power Pivot khác nhau ở đâu?

Tiêu chí	Power Query	Power Pivot
Câu hỏi cần giải quyết	Dữ liệu đã sạch và đúng cấu trúc chưa?	Các bảng liên hệ thế nào, chỉ số được tính ra sao?
Việc thường làm	Đổi kiểu, bỏ khoảng trắng, gộp file, ghép bảng	Tạo quan hệ, cột tính toán và measure
Ngôn ngữ	M	DAX
Đầu ra	Bảng dữ liệu để nạp hoặc kết nối	Mô hình phục vụ phân tích trong Excel
Ví dụ	Chuẩn hóa mã cửa hàng	Tính biên lợi nhuận theo cửa hàng đang chọn

Bạn có thể hình dung Power Query là nơi chuẩn bị nguyên liệu, còn Power Pivot là nơi tổ chức dữ liệu phục vụ phân tích. PivotTable là lớp trình bày: kéo trường vào hàng, cột và vùng giá trị để đọc kết quả. Power Pivot và PivotTable vì thế cũng không phải một tên gọi cho cùng tính năng.

Trong hướng dẫn kết hợp hai công cụ của Microsoft, quá trình chuẩn bị dữ liệu và xây dựng mô hình được mô tả thành các phần bổ trợ nhau. Điểm cần quyết định khi làm báo cáo là đặt mỗi phép xử lý ở đúng phần.

Khi nào chỉ cần một công cụ?

Nếu mỗi tháng bạn nhận một file bán hàng, cần bỏ dòng trống, sửa kiểu ngày rồi xuất bảng sạch, Power Query có thể đáp ứng. Chưa cần tạo mô hình nếu đầu ra chỉ là một danh sách để gửi kế toán hoặc làm nguồn cho công thức Excel.

Nếu dữ liệu đã sạch nhưng nằm trong nhiều bảng, bạn cần phân tích theo danh mục và dùng lại chỉ số ở nhiều báo cáo, hãy cân nhắc Power Pivot. Chẳng hạn bảng giao dịch chứa mã hàng, bảng danh mục chứa ngành hàng; quan hệ giúp phân tích doanh thu theo ngành mà không phải sao chép tên ngành vào mọi dòng.

Không nên chọn theo số lượng công cụ học được. Hãy viết rõ đầu ra trước: bảng sạch để xuất file, hay báo cáo tương tác theo nhiều chiều? Nếu chưa quen thao tác nhập và biến đổi, bạn có thể xem cách dùng Power Query Editor trước khi làm tiếp.

Thực hành: từ bảng bán hàng đến biên lợi nhuận

1. Chuẩn bị dữ liệu và quy ước

Ví dụ dành cho Excel trên Windows có Power Query và Power Pivot. Khả năng sử dụng tùy phiên bản, giấy phép và nền tảng; hãy kiểm tra phạm vi hỗ trợ trong Excel trước khi tìm nút trên máy. Không mặc định bản Mac có đủ quy trình giống Windows.

Tạo bảng Excel bằng Ctrl+T, đặt tên tblBanHang, dùng các tiêu đề bên dưới. Nhập số nguyên không có dấu phân cách hàng nghìn. Mỗi dòng là một giao dịch; DoanhThu và GiaVon là tổng tiền của dòng đó.

MaGD	MaCH	DoanhThu	GiaVon
GD01	HN	1000000	600000
GD02	HN	2000000	1600000
GD03	DN	7000000	6300000

Tạo thêm bảng tblCuaHang với hai cột MaCH, TenCH: HN tương ứng Hà Nội; DN tương ứng Đà Nẵng. Mỗi mã xuất hiện đúng một lần trong bảng này.

2. Dùng Power Query để chuẩn hóa

Chọn ô trong tblBanHang, vào Data → From Table/Range. Trong Advanced Editor, thay nội dung bằng mã M sau. Mã đọc đúng bảng vừa tạo, chuẩn hóa mã cửa hàng, định kiểu số và thêm lợi nhuận gộp từng dòng.

let
    Source = Excel.CurrentWorkbook(){[Name="tblBanHang"]}[Content],
    CleanCode = Table.TransformColumns(
        Source,
        {{"MaCH", each Text.Upper(Text.Trim(_)), type text}}
    ),
    Typed = Table.TransformColumnTypes(
        CleanCode,
        {{"MaGD", type text}, {"DoanhThu", Int64.Type}, {"GiaVon", Int64.Type}}
    ),
    AddProfit = Table.AddColumn(
        Typed, "LoiNhuan", each [DoanhThu] - [GiaVon], Int64.Type
    )
in
    AddProfit

Đặt tên truy vấn là BanHang. Kết quả phải có ba dòng, cột LoiNhuan lần lượt là 400000, 400000 và 700000. Microsoft mô tả Table.AddColumn là thao tác thêm cột từ biểu thức tính cho mỗi dòng; ở đây ta dùng nó cho phép trừ đơn giản, cố định theo giao dịch.

Chọn Close & Load To, chọn Only Create Connection và đánh dấu Add this data to the Data Model. Với tblCuaHang, tạo truy vấn CuaHang, chuẩn hóa MaCH tương tự rồi nạp vào Data Model. Nếu thêm dữ liệu vào bảng nguồn sau này, phải làm mới để các bước được áp dụng lại.

3. Tạo quan hệ giữa hai bảng

Mở Power Pivot → Manage → Diagram View. Nối CuaHang[MaCH] với BanHang[MaCH]. CuaHang là phía một vì mã không trùng; BanHang là phía nhiều vì một cửa hàng có nhiều giao dịch.

Hai cột mã phải cùng kiểu dữ liệu, và mọi mã giao dịch phải có trong danh mục. Nếu bảng CuaHang có hai dòng HN, hãy xác minh nguyên nhân trước khi xóa trùng. Bạn không nên giữ ngẫu nhiên một dòng nếu hai dòng đang chứa tên hay thuộc tính mâu thuẫn.

Tài liệu quan hệ bảng của Microsoft giải thích yêu cầu về cột khóa duy nhất. Quan hệ không ghép thêm cột vật lý như Merge trong Power Query; nó cho phép các bảng tham gia phân tích cùng nhau.

4. Tạo measure trong Power Pivot

Trong vùng tính toán của bảng BanHang, tạo lần lượt các measure dưới đây. Cú pháp dùng dấu phẩy; nếu thiết lập vùng của Excel yêu cầu dấu chấm phẩy, đổi dấu phân cách đối số tương ứng.

Doanh thu := SUM(BanHang[DoanhThu])

Loi nhuan := SUM(BanHang[LoiNhuan])

Bien loi nhuan := DIVIDE([Loi nhuan], [Doanh thu])

Định dạng hai measure đầu là số tiền, measure cuối là phần trăm. Khi dùng hộp thoại New Measure, nhập tên ở ô tên và chỉ nhập phần sau dấu := vào ô công thức, bắt đầu bằng dấu =.

Tạo PivotTable từ Data Model, kéo CuaHang[TenCH] vào Rows và ba measure vào Values. Theo tài liệu Microsoft, SUM cộng một cột số; DIVIDE giúp xử lý phép chia có mẫu số bằng không. Ví dụ này để DIVIDE trả kết quả trống khi không tính được tỷ lệ.

Đối chiếu kết quả: đừng lấy trung bình các tỷ lệ

Cửa hàng	Doanh thu	Lợi nhuận gộp	Biên lợi nhuận
Hà Nội	3.000.000	800.000	26,67%
Đà Nẵng	7.000.000	700.000	10,00%
Tổng	10.000.000	1.500.000	15,00%

Lấy trung bình hai tỷ lệ cửa hàng sẽ ra khoảng 18,33%, khác 15%. Nguyên nhân là doanh thu hai cửa hàng không bằng nhau. Measure tính lại phép chia ở dòng tổng nên cho đúng kết quả tổng lợi nhuận chia tổng doanh thu.

Đây cũng là lý do nên giữ tỷ lệ ở dạng measure thay vì chỉ lưu tỷ lệ từng dòng rồi chọn Average. Bạn có thể đọc tiếp cách tạo measure DAX trong Excel nếu muốn mở rộng bài toán.

Kiểm tra trước khi dùng báo cáo thật

• Đếm số dòng trước và sau biến đổi; điều tra nếu số dòng tăng ngoài dự kiến.
• So tổng doanh thu, giá vốn với nguồn; trong ví dụ lần lượt là 10 triệu và 8,5 triệu đồng.
• Lọc từng cửa hàng để kiểm tra quan hệ có truyền bộ lọc đúng không.
• Thêm một giao dịch thử vào bảng nguồn, chọn Refresh All, chờ hoàn tất và đối chiếu lại.
• Kiểm tra thông báo lỗi chuyển kiểu; không thay mọi lỗi bằng số 0 chỉ để bảng hết báo lỗi.

Nếu file dùng chung, ghi lại nguồn dữ liệu, tên bảng, công thức và cách làm mới. Người nhận cần biết ngày dữ liệu được cập nhật, thay vì chỉ nhìn một biểu đồ đẹp rồi mặc định số liệu đã mới.

Câu hỏi thường gặp

Power Query có dùng DAX không?

Không. Công thức trong Power Query dùng M. Trong ví dụ, bước thêm LoiNhuan dùng M; measure tổng hợp và tỷ lệ trong Power Pivot dùng DAX.

Có bắt buộc dùng Power Pivot sau Power Query không?

Không. Bạn có thể nạp bảng sạch ra worksheet rồi dùng công thức hoặc PivotTable thông thường. Chỉ thêm mô hình khi yêu cầu phân tích cần tới nó.

Vì sao sửa bảng nguồn mà PivotTable chưa đổi?

Hãy làm mới truy vấn và báo cáo, đợi hoàn tất, rồi kiểm tra dữ liệu mới có nằm trong Table nguồn không. Đồng thời xem truy vấn có lỗi hoặc bước lọc loại mất dòng mới hay không.

Học công cụ nào trước?

Nên bắt đầu bằng Power Query với một bảng nhỏ, tập đổi kiểu và đối chiếu tổng. Sau đó học quan hệ và measure trong Power Pivot bằng chính bộ dữ liệu đã hiểu.

Nếu bạn cần người chuẩn hóa nguồn và dựng báo cáo Power BI từ quy trình Excel hiện tại, hãy xem báo giá làm Power BI và chuẩn bị một file mẫu đã ẩn dữ liệu nhạy cảm cùng các chỉ số cần theo dõi.