Bảng tính 4 — Power Query: ETL trong Excel

Vì sao Power Query thay công thức thủ công

Trong ba bài trước của series — Tổng quan bảng tính, Công thức nâng cao và PivotTable — ta đã thấy giới hạn của cách làm truyền thống: mỗi tháng lại copy dữ liệu mới, kéo lại công thức, sửa tay vài chỗ, rồi cầu nguyện không sót dòng. Cách này không lặp lại được và không audit được: sếp hỏi "con số này ra từ đâu?" thì không ai trả lời chính xác.

Power Query giải quyết đúng vấn đề đó. Nó là công cụ ETL (Extract — Transform — Load) self-service tích hợp sẵn trong Excel (từ 2016, ribbon Data → Get Data) và trong Power BI (là engine chuẩn bị dữ liệu). Thay vì viết công thức trên từng ô, bạn mô tả một quy trình biến đổi: kết nối nguồn → làm sạch → biến đổi → nạp kết quả. Quy trình này được ghi lại thành chuỗi Applied Steps và có thể chạy lại toàn bộ chỉ bằng một nút Refresh.

Bốn đặc tính khiến Power Query khác hẳn công thức thủ công:

• Lặp lại được (repeatable): tháng sau có dữ liệu mới, bạn không làm lại gì cả — chỉ Refresh. Toàn bộ chuỗi bước tự chạy lại trên dữ liệu mới.
• Không phá dữ liệu gốc (non-destructive): Power Query đọc từ nguồn và tạo ra bảng kết quả riêng. File CSV, database gốc không bị đụng tới. Sai thì xoá bước, làm lại — không mất dữ liệu.
• Audit được: mỗi bước biến đổi hiện rõ trong pane Applied Steps bên phải. Bất kỳ ai mở query cũng thấy chính xác dữ liệu đã đi qua những phép biến đổi nào, theo thứ tự nào.
• Quy mô lớn hơn công thức: xử lý hàng trăm nghìn dòng, gộp hàng chục file, mà không làm file Excel phình lên vì công thức.

Trên Google Sheets không có Power Query. Tương đương gần nhất là Connected Sheets (truy vấn trực tiếp BigQuery từ giao diện Sheets) cho phần kết nối nguồn lớn, và Apps Script cho phần biến đổi/tự động hoá. Đây là điểm khác biệt lớn: hệ sinh thái Microsoft mạnh hơn hẳn về ETL trong bảng tính.

Kết nối nguồn dữ liệu

Sức mạnh đầu tiên của Power Query nằm ở số lượng nguồn nó nối được. Với một analyst ngân hàng, các nhóm nguồn quan trọng là:

Hai nguồn đáng chú ý nhất trong thực tế:

From Folder — trỏ vào một thư mục, Power Query liệt kê mọi file trong đó và cho phép gộp (combine) tất cả file cùng cấu trúc thành một bảng duy nhất. Đây là lời giải kinh điển cho bài toán "12 file sao kê mỗi tháng một file". Bạn định nghĩa cách xử lý một file mẫu, Power Query áp dụng cho tất cả file — kể cả file phát sinh tháng sau, chỉ cần Refresh.

From Database — kết nối tới SQL Server / PostgreSQL / ODBC để kéo dữ liệu gốc. Khác biệt quan trọng: khi nối database, Power Query có thể đẩy phép biến đổi ngược về database để chạy (query folding — mục riêng bên dưới), thay vì tải hết dữ liệu về máy rồi mới lọc. Câu SQL nguồn thường trông như thế này (đây là SQL PostgreSQL thật, chạy được trên sandbox đọc-chỉ):

-- ▶ Chạy được
SELECT a.currency,
       COUNT(*)                       AS so_giao_dich,
       ROUND(AVG(t.amount)::numeric, 2) AS gia_tri_tb,
       SUM(t.amount)                  AS tong_gia_tri
FROM transactions t
JOIN accounts a ON a.id = t.account_id
GROUP BY a.currency
ORDER BY tong_gia_tri DESC;

Câu này lấy dữ liệu ở mức tổng hợp về cho Power Query nạp tiếp. Trong thực tế, thường ta để Power Query tự sinh SQL qua query folding thay vì viết tay, nhưng hiểu SQL nguồn giúp bạn kiểm soát và tối ưu.

Các phép biến đổi cốt lõi

Power Query Editor là nơi bạn "làm sạch và nắn" dữ liệu. Mỗi thao tác trên ribbon = một Applied Step. Bộ phép biến đổi cốt lõi cần thuộc lòng:

Làm sạch cột đơn giản

• Change Type (đổi kiểu): ép cột về đúng kiểu (text, số nguyên, số thập phân, ngày, boolean). Nên làm sớm ngay sau khi nạp, vì nhiều bước sau phụ thuộc kiểu đúng.
• Split Column / Merge Columns (tách/gộp cột): tách "NGUYEN VAN A - 0123456" thành tên và số tài khoản; hoặc ngược lại.
• Replace Values (thay thế): đổi "N/A" thành null, chuẩn hoá "TP.HCM"/"HCM"/"Hồ Chí Minh".
• Filter Rows (lọc): bỏ dòng tổng, dòng trống, dòng tiêu đề lặp.
• Remove Duplicates (xoá trùng): khử bản ghi lặp theo một hay nhiều cột khoá.
• Fill Down (điền xuống): lấp các ô trống bằng giá trị phía trên — cực hữu ích với báo cáo có ô gộp (merged cell) mà chỉ dòng đầu nhóm mới có giá trị.
• Conditional Column (cột điều kiện): tạo cột mới theo luật if/then (ví dụ phân nhóm nợ theo số ngày quá hạn), không cần viết công thức M.
• Column From Examples (cột từ ví dụ): bạn gõ vài giá trị mẫu, Power Query đoán logic và sinh cột — rất nhanh cho tách chuỗi phức tạp.

Biến đổi cấu trúc bảng — nhóm quan trọng nhất

• Unpivot: biến bảng ngang (mỗi tháng một cột) thành dạng dài (mỗi dòng là một cặp thuộc tính–giá trị). Đây là phép biến đổi đắt giá nhất của Power Query với dân báo cáo: hầu hết báo cáo Excel được trình bày dạng ma trận ngang cho người đọc, nhưng để pivot lại được hay đổ vào data model thì phải ở dạng dài. Ngược lại là Pivot (dài → ngang).
• Group By: tổng hợp như GROUP BY của SQL — nhóm theo cột và tính SUM/COUNT/AVG/MIN/MAX.
• Merge Queries (~ JOIN): ghép hai bảng theo cột khoá. Hỗ trợ đầy đủ các kiểu join — Left Outer, Right Outer, Full Outer, Inner, Left Anti, Right Anti. Ví dụ ghép sao kê với danh mục chi nhánh.
• Append Queries (~ UNION): nối nhiều bảng cùng cấu trúc theo chiều dọc — chính là cách gộp 12 file sao kê tháng thành một.

Bảng dưới đối chiếu nhanh với SQL để bạn dễ nhớ:

Ngôn ngữ M và Advanced Editor

Mỗi Applied Step thực chất là một dòng ngôn ngữ M (Power Query Formula Language). Bạn xem/sửa toàn bộ query trong Advanced Editor (View → Advanced Editor). Cấu trúc M dựa trên let ... in: khai báo một chuỗi bước, mỗi bước là một biểu thức tham chiếu bước trước.

let
    Source = Csv.Document(File.Contents("C:\SaoKe\thang01.csv"),
        [Delimiter=",", Encoding=65001]),
    Headers = Table.PromoteHeaders(Source),
    ChangeType = Table.TransformColumnTypes(Headers, {
        {"ngay_gd", type date},
        {"so_tien", type number}
    }),
    NoBlanks = Table.SelectRows(ChangeType, each [so_tien] <> null),
    Filtered = Table.SelectRows(NoBlanks, each [so_tien] > 0)
in
    Filtered

Vài điểm cần nắm về M:

• Mỗi phép trên ribbon sinh ra đúng một dòng M — tên bước (ChangeType, Filtered...) chính là tên bạn thấy trong Applied Steps.
• M phân biệt hoa/thường và có kiểu dữ liệu chặt chẽ hơn công thức Excel.
• Ký hiệu each là cú pháp rút gọn cho hàm một tham số; [cot] truy cập giá trị cột trong dòng hiện tại.
• Bạn hiếm khi phải gõ M từ đầu — ribbon lo phần lớn. Nhưng biết đọc M giúp bạn sửa nhanh, tái sử dụng, và hiểu vì sao một bước phá query folding.

Các khối M ở trên chỉ để minh hoạ, không chạy trên SQL Builder của app.

Parameter và hàm tái sử dụng

Đường dẫn cứng như C:\SaoKe\thang01.csv là cạm bẫy: đổi máy, đổi thư mục là gãy. Parameter (Manage Parameters → New Parameter) tách các giá trị hay đổi ra ngoài:

• Tham số đường dẫn thư mục (RootFolder): chuyển file sang máy khác chỉ cần sửa một chỗ.
• Tham số kỳ báo cáo (KyBaoCao = "2026-06"): dùng chung cho nhiều query, đổi kỳ một lần là cả mô hình cập nhật.

Cao hơn nữa là hàm tuỳ biến (custom function): chuyển một query thành hàm nhận tham số. Đây chính là cơ chế đứng sau tính năng combine folder — Power Query tạo một hàm "xử lý một file" rồi áp cho mọi file trong thư mục. Bạn cũng có thể tự viết hàm fnLamSachSaoKe(file) và gọi lại nhiều nơi, giữ logic ở một chỗ duy nhất (DRY).

Query folding — chạy nhanh khi nối database

Query folding là khả năng Power Query dịch các Applied Steps thành câu SQL và đẩy về database chạy, thay vì tải toàn bộ bảng về máy rồi mới xử lý. Với dữ liệu ngân hàng hàng chục triệu dòng, đây là ranh giới giữa "refresh 5 giây" và "refresh 20 phút".

Ví dụ: bạn nối PostgreSQL, chọn bảng transactions, lọc theo tháng, Group By theo account_id. Nếu folding hoạt động, Power Query gửi một câu SELECT ... WHERE ... GROUP BY ... xuống PostgreSQL — database chỉ trả về vài nghìn dòng tổng hợp. Nếu folding bị phá vỡ, nó kéo cả bảng về rồi mới lọc/nhóm tại chỗ — chậm và tốn RAM.

Nguyên tắc giữ folding:

• Đặt các bước "database làm được" (filter, group, merge trên cùng nguồn) lên trước.
• Tránh các thao tác database không hiểu: thêm cột bằng M phức tạp, Table.Buffer, một số bước Column From Examples — chúng phá folding, mọi bước sau đó chạy tại máy.
• Kiểm tra bằng cách chuột phải một bước → View Native Query. Nếu mờ (disabled), folding đã dừng ở đó.

Folding chỉ áp dụng cho nguồn có "engine" phía sau (database, OData). File CSV/Excel không fold — mọi biến đổi chạy trên máy. Xem thêm bối cảnh ở Kiến trúc PostgreSQL.

Refresh và lịch làm mới

Nút Refresh chạy lại toàn bộ chuỗi Applied Steps trên dữ liệu nguồn hiện tại:

• Refresh thủ công: Data → Refresh All trong Excel.
• Refresh khi mở file hoặc theo lịch (mỗi N phút): cấu hình trong Query Properties.
• Refresh nền / theo lịch tập trung: khi đưa lên Power BI Service hoặc SharePoint, có scheduled refresh và gateway để nối nguồn on-premise. Xem Power BI tổng quan và DirectQuery/storage.

Điểm mấu chốt: nguồn phải ở đúng vị trí và cấu trúc không đổi. Đây là lý do dùng parameter cho đường dẫn và giữ tên cột ổn định — Refresh sẽ gãy nếu file bị đổi tên cột hay dời thư mục.

Power Query vs PivotTable vs công thức — dùng cái nào?

Ba công cụ này bổ sung chứ không thay thế nhau. Ranh giới rất rõ:

Quy trình chuẩn: Power Query làm sạch → nạp vào bảng/Data Model → PivotTable tổng hợp → công thức điểm xuyết. Đưa việc làm sạch lên Power Query giúp file gọn, nhanh và audit được, thay vì nhồi hết vào công thức như bài Công thức nâng cao. Về xây data model và DAX sau bước này, xem Data Model & DAX.

Sơ đồ pipeline Power Query

Mẹo và cạm bẫy

• Đổi kiểu sớm, đổi một lần. Change Type ngay sau khi nạp; tránh Power Query tự chèn nhiều bước "Changed Type" rải rác — vừa rối vừa dễ lỗi locale.
• Cẩn thận kiểu ngày theo locale. File từ hệ thống lõi ngân hàng thường là dd/MM/yyyy; máy đặt locale US (MM/dd/yyyy) sẽ đọc sai âm thầm. Dùng Change Type → Using Locale để chỉ định rõ.
• Tránh đường dẫn cứng. Luôn tham số hoá đường dẫn thư mục và kỳ báo cáo.
• Đừng phá query folding vô cớ. Với nguồn database, đặt filter/group/merge lên trước; kiểm tra View Native Query.
• Đặt tên bước có nghĩa. LocSaoKeDuong, GopChiNhanh... thay vì để tên mặc định — người sau (và bạn ba tháng sau) sẽ cảm ơn.
• Combine folder cần các file đồng nhất cột. Một file lệch tên cột là Append/Combine gãy. Chuẩn hoá tên cột ở file mẫu trước.
• Xoá cột không cần sớm để giảm dữ liệu tải và giữ folding gọn.

Use case thực tế

Bối cảnh: Bộ phận báo cáo cần một dashboard giao dịch hợp nhất từ 12 file sao kê tháng (mỗi chi nhánh xuất một file CSV/tháng vào cùng một thư mục \\fileserver\saoke\2026\), cộng với một báo cáo cân đối kế toán đang ở dạng ma trận ngang (cột: các tháng; dòng: khoản mục).

Pipeline Power Query dựng một lần:

1. From Folder trỏ vào \\fileserver\saoke\2026\ → Power Query liệt kê 12 file.
2. Combine & Transform: định nghĩa xử lý một file mẫu — Promote Headers, Change Type (ngay_gd = date theo locale VN, so_tien = number).
3. Filter Rows: bỏ dòng tổng cuối trang và dòng trống; Remove Duplicates theo mã giao dịch.
4. Append: 12 file tự động nối thành một bảng ~480.000 dòng (giả định 40k/tháng).
5. Merge với query danh mục chi nhánh (Left Outer trên ma_cn) để có tên và vùng.
6. Nạp vào Data Model cho PivotTable/dashboard.

Với báo cáo cân đối ngang: chọn cột khoản mục làm khoá, Unpivot 12 cột tháng thành hai cột Thang và GiaTri. Bảng dạng dài này giờ pivot lại được, vẽ được xu hướng theo tháng, và merge được với bảng khác.

Kết quả: cuối tháng 7, chi nhánh thả file thang07.csv vào thư mục và nhấn Refresh All. Toàn bộ 6 bước chạy lại, dashboard cập nhật trong ~30 giây, không sửa một công thức nào. Trước đây quy trình copy-paste-kéo-công-thức mất nửa buổi và thường sai sót ở khâu ghép chi nhánh. Về khía cạnh kiểm soát dữ liệu đầu vào, xem Chất lượng dữ liệu.

Ghi nhớ

• Power Query là ETL self-service trong Excel & Power BI: kết nối → làm sạch → biến đổi → nạp, tất cả lặp lại được bằng một nút Refresh.
• Ưu thế cốt lõi so với công thức: repeatable, non-destructive, audit được qua Applied Steps.
• Kết nối đa nguồn: file, folder (gộp nhiều file cùng dạng), database, web/API. Folder + Append là lời giải kinh điển cho "mỗi tháng một file".
• Bộ biến đổi cần thuộc: Change Type, Split/Merge cột, Replace, Filter, Remove Duplicates, Fill Down, Unpivot (ngang→dài, để pivot lại được), Group By, Merge (JOIN), Append (UNION), Conditional/From Examples.
• Mỗi bước là một dòng ngôn ngữ M; xem/sửa trong Advanced Editor (let ... in). Ribbon lo phần lớn, nhưng biết đọc M rất đáng.
• Parameter tách đường dẫn/kỳ báo cáo; custom function để tái sử dụng logic.
• Query folding: đẩy biến đổi về database chạy — sống còn khi nối DB lớn; đừng phá nó bằng bước M phức tạp đặt sớm.
• Phân vai rõ: Power Query = biến đổi, PivotTable = tổng hợp, công thức = tính lẻ.
• Cạm bẫy hay gặp: đổi kiểu muộn, locale ngày sai âm thầm, đường dẫn cứng, file lệch cột phá Combine.