2026-10-04 · Power Query & Excel
Mở Advanced Editor của một query đang chạy tốt, màn hình đầy những dòng như #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom", …). Câu hỏi đầu tiên của nhiều bạn làm kế toán: có phải học thứ này mới dùng được Power Query không?
Để trả lời bằng file thật, đội ngũ EzTech đọc lại phần mã M nằm trong ba file Power Query đã dựng cho các bài hướng dẫn. Một file hạch toán doanh thu và phí sàn thương mại điện tử vào phần mềm kế toán, một file lấy tỷ giá từ web ngân hàng, một file báo cáo lãi lỗ tự cập nhật từ sổ nhật ký chung. Ba file có 14 query, 147 bước. Không bước nào được đặt tên bằng tay: tên nào cũng là tên Power Query tự đặt khi bấm nút, nhiều nhất là "Changed Type" (31 bước) và "Added Custom" (25 bước).
Vậy câu trả lời ngắn: dùng Power Query không cần biết viết ngôn ngữ M. Nhưng đọc được M và sửa được vài chỗ thì xử lý được phần lớn những lần query hỏng, và đó là phần đáng bỏ thời gian học.
Ngôn ngữ M là gì
Microsoft mô tả M là ngôn ngữ dùng để diễn đạt mọi thao tác lấy và kết hợp dữ liệu trong Power Query, một ngôn ngữ hàm và có phân biệt chữ hoa chữ thường. Power Query trong Excel, Power BI Desktop và nhiều dịch vụ khác của Microsoft dùng chung ngôn ngữ này.
Mỗi query là một đoạn M. Mỗi lần bạn bấm một nút trong Power Query Editor (lọc dòng, đổi kiểu, thêm cột), giao diện ghi thêm một bước vào đoạn M đó. Danh sách Applied Steps ở khung bên phải là danh sách các bước M đó. Muốn xem toàn bộ đoạn mã: thẻ Home → Advanced Editor. Muốn xem riêng một bước: bật thanh công thức ở thẻ View → Formula Bar rồi bấm vào bước đó.
Đọc một query M trong 5 phút
Ví dụ dưới đây là query gom 24 file bán hàng theo tháng trong báo cáo Power BI mẫu của EzTech, query này được viết thẳng bằng M nên tên bước là tiếng Việt. Phần danh sách cột và đường dẫn đã rút gọn cho dễ đọc.
| Dòng M | Nghĩa là |
|---|---|
let | Mở đầu danh sách các bước |
DataFolder = "D:\…\Data ban hang theo thang", | Đặt đường dẫn thư mục vào một biến, chỉ khai một lần |
Source = Folder.Files(DataFolder), | Lấy danh sách file trong thư mục đó |
#"Đã lọc file bán hàng" = Table.SelectRows(Source, each Text.StartsWith([Name], "Ban hang") and [Extension] = ".xlsx" and not Text.StartsWith([Name], "~$")), | Giữ file có tên bắt đầu bằng "Ban hang", đuôi .xlsx, bỏ file tạm đang mở |
#"Đã đọc sheet Data" = Table.AddColumn(#"Đã lọc file bán hàng", "NoiDung", each Excel.Workbook([Content], true){[Item = "Data", Kind = "Sheet"]}[Data]), | Mở từng file, lấy bảng ở sheet tên Data |
#"Đã gộp toàn bộ file" = Table.Combine(#"Đã đọc sheet Data"[NoiDung]), | Nối bảng của 24 file thành một |
#"Đã chọn cột cần dùng" = Table.SelectColumns(…, {"Ngày bán", "Số HĐ", …}), | Giữ 14 cột cần dùng |
#"Đã chuẩn hóa kiểu dữ liệu" = Table.TransformColumnTypes(…) | Đặt kiểu ngày, chữ, số cho từng cột |
in #"Đã chuẩn hóa kiểu dữ liệu" | Kết quả của query là bước cuối cùng này |
Từ bảng trên rút ra được gần hết những gì cần để đọc M:
- let … in. Giữa
letvàinlà các bước. Sauinlà tên bước cho ra kết quả, thường là bước cuối. - Tên bước = biểu thức, kết thúc bằng dấu phẩy, trừ bước cuối. Bước sau gọi bước trước bằng tên, nên khi sửa tay trong Advanced Editor mà đổi tên một bước thì phải đổi luôn ở mọi chỗ gọi nó.
- #"…" bao quanh tên có dấu cách, như
#"Đã lọc file bán hàng". - [Tên cột] là giá trị của cột đó ở dòng đang xét. each là cách viết tắt của một hàm nhận một tham số: theo tài liệu Microsoft,
each [CustomerID]tương đương(_) => _[CustomerID]. - { } là danh sách.
{0}lấy phần tử đầu tiên (M đếm từ 0).{[Item = "Data", Kind = "Sheet"]}chọn dòng có Item là "Data" và Kind là "Sheet", rồi[Data]lấy cột Data của dòng đó. - // mở đầu một dòng chú thích, Power Query bỏ qua khi chạy.
- Hoa thường khác nhau:
Table.SelectRowsvàtable.selectrowslà hai tên khác nhau,[Tên hàng]và[tên hàng]cũng vậy.
Ba file thật: giao diện viết M như thế nào
| File | Query · bước | Điều đáng chú ý |
|---|---|---|
| Hạch toán doanh thu, phí sàn vào phần mềm kế toán | 5 · 88 | Ba query hạch toán mỗi query 21 bước, khác nhau 7 đến 8 dòng |
| Lấy tỷ giá từ web ngân hàng | 2 · 14 | Tên cột chứa ký tự xuống dòng và tab lấy từ trang web |
| Báo cáo lãi lỗ tự cập nhật | 7 · 45 | 4 query phụ do nút Combine Files tạo ra, 4 chỗ ghi cứng đường dẫn |
Ba điểm đọc ra được từ chính mã:
- Giao diện ghi mỗi thao tác thành một bước. Có 25 lần "thêm cột tính" rồi tới bước kế tiếp mới "đổi kiểu dữ liệu" cho cột đó, và 10 lần thêm hoặc nhân bản cột rồi bước sau mới đổi tên. Query vẫn chạy đúng, chỉ dài hơn cần thiết. Người đọc được M biết hai bước đó gộp được: hàm
Table.AddColumncó tham số thứ tư để khai kiểu ngay khi tạo cột. - Ký tự ẩn hiện thành mã. Trong file tỷ giá, cột "Mua tiền mặt" có tên thật là
"Mua tiền mặt#(lf)#(tab)…#(tab)Mua tiền mặt", với 9 lần#(tab). Theo đặc tả M,#(lf)là ký tự xuống dòng,#(tab)là ký tự tab: ô tiêu đề trên trang web có xuống dòng và thụt lề, Power Query giữ nguyên. Tên dài đó lặp lại ở 4 bước, trang web đổi cách trình bày là cả 4 bước gãy. - Nút Combine Files sinh thêm query phụ. Bốn query Parameter1, Sample File, Transform Sample File, Transform File trong file lãi lỗ là do Power Query tự tạo khi gộp file. Tài liệu Microsoft giải thích đây đúng là cơ chế hàm tùy biến (custom function) mà giao diện dựng sẵn cho bạn.
Bốn chỗ nên học để tự sửa M
1. Đường dẫn file và thư mục
File báo cáo lãi lỗ có 4 chuỗi đường dẫn tuyệt đối dạng "D:\…": hai chỗ trỏ tới thư mục sổ nhật ký chung, hai chỗ trỏ tới file danh mục. Chuyển cả bộ file sang thư mục khác là phải sửa đủ 4 chỗ, sót một chỗ là refresh báo lỗi không tìm thấy đường dẫn (lỗi số 4 trong bài 7 lỗi Power Query thường gặp). Query trong báo cáo Power BI mẫu ở trên đặt đường dẫn vào biến DataFolder ở đầu, kèm dòng chú thích, nên chỉ có một chỗ phải sửa. Cách làm bằng giao diện: Home → Manage Parameters, tạo một tham số chứa đường dẫn rồi dùng tham số đó thay cho chuỗi.
2. Danh sách cột viết cứng
Bước Changed Type và Removed Other Columns ghi tên từng cột. File nguồn tháng sau đổi tên một cột là query dừng lại. Table.SelectColumns có tham số MissingField.UseNull: cột không còn thì tạo cột rỗng thay vì báo lỗi. Tài liệu hiện hành của Microsoft ghi Table.TransformColumnTypes cũng nhận một record ở tham số thứ ba, ví dụ [MissingField = MissingField.UseNull]. Cái giá là cột thiếu sẽ thành cột rỗng mà không ai báo, nên cần thêm một phép đếm ô trống sau khi refresh. Cách dùng chi tiết có ở bài gộp file Excel khác cấu trúc.
3. Bắt lỗi từng ô bằng try … otherwise
Power Query có cú pháp riêng tương tự IFERROR của Excel. Ví dụ trong tài liệu Microsoft: cột tính try [Standard Rate] otherwise [Special Rate] lấy giá trị cột Standard Rate, ô nào lỗi thì lấy cột Special Rate. Viết kiểu này trong hộp Add Column → Custom Column, không cần mở Advanced Editor. Từ tháng 5/2022 còn có thêm từ khóa catch để xử lý theo từng loại lỗi.
4. Cùng một chuỗi bước lặp lại cho nhiều bảng
Trong file hạch toán, ba query cho doanh thu, phí vận chuyển và phí sàn giống nhau gần như từng dòng: mỗi query 21 bước, chỉ khác 6 giá trị (tên cột số tiền, tiền tố số chứng từ, câu diễn giải, tài khoản Nợ, tài khoản Có, mã khoản mục chi phí). Thêm một loại phí mới là bấm lại 21 bước nữa, sửa logic chung là sửa ba nơi.
M cho phép gom phần lặp vào một hàm tùy biến nhận các giá trị khác nhau làm tham số, rồi gọi hàm đó ba lần. Theo hướng dẫn của Microsoft, không cần viết hàm từ đầu: thay các giá trị cố định bằng tham số (Manage Parameters), chuột phải vào query → Create Function, rồi dùng Add Column → Invoke Custom Function. Một lưu ý cũng nằm trong tài liệu đó: muốn sửa logic thì sửa query mẫu (như Transform Sample File), đừng sửa thẳng vào hàm, vì sửa thẳng thì hàm thôi cập nhật theo query mẫu.
Học M đến đâu là đủ
| Mức | Làm được | Khi nào cần |
|---|---|---|
| 1. Đọc | Hiểu let … in, tên bước, [cột], each, { }; biết query hỏng ở bước nào | Ngay khi bắt đầu dùng Power Query đều đặn |
| 2. Sửa | Đổi đường dẫn, danh sách cột, điều kiện lọc; thêm try … otherwise; dùng tham số | Khi query phải chạy lại mỗi tháng trên file người khác gửi |
| 3. Viết hàm | Gom chuỗi bước lặp thành hàm, gọi cho nhiều bảng, nhiều file | Khi cùng một logic lặp ở từ ba query trở lên |
Mức 1 và 2 đủ cho phần lớn việc kế toán, tài chính. Tra cú pháp từng hàm ở tài liệu ngôn ngữ M của Microsoft, mỗi hàm có ví dụ chạy được ngay.
Câu hỏi thường gặp
M khác DAX thế nào?
M chạy khi refresh, để lấy dữ liệu và dọn nó thành bảng sạch. DAX chạy sau khi dữ liệu đã nạp vào mô hình, để tính measure và cột tính trong Power BI hoặc Power Pivot. Cùng một việc, thường làm được bằng cả hai; nên làm sạch ở M, tính toán ở DAX. Phần DAX có ở bài SUM và SUMX trong Power BI.
Nhờ AI viết M được không?
Được, và khá hiệu quả với những đoạn ngắn. Hộp Custom Column có báo "No syntax errors have been detected" khi công thức đúng cú pháp, nhưng đúng cú pháp chưa có nghĩa là đúng số. Sau khi dán mã AI viết, đối chiếu số dòng và tổng tiền với file nguồn trước khi dùng. Cách kiểm có ở bài AI bịa số: 5 cách kiểm tra.
Có cần học VBA trước khi học M không?
Không. Hai thứ giải hai bài toán khác nhau, và Power Query dùng được ngay mà không cần biết dòng mã nào. So sánh chi tiết ở bài Power Query hay VBA.
Bắt đầu từ query của chính bạn
Cách học M nhanh nhất là mở Advanced Editor của một query bạn đã bấm ra, đọc từng dòng đối chiếu với Applied Steps. Khóa DATA FINANCE của EzTech đi từ Power Query chuyên sâu trong Excel đến báo cáo tài chính, quản trị trên Power BI, có phần xử lý những query phải chạy lại hằng tháng. Muốn biết mình đang ở mức nào, làm thử bài trắc nghiệm Power Query miễn phí.
