Microsoft Power Query Cho Excel: Khám Phá Sức Mạnh Của Công Cụ Etl Trong Tầm Tay Người Dùng

Có thể bạn quan tâm: Microsoft Excel 2026 Free Download: Hướng Dẫn Chi Tiết, Lý Do Nên Sử Dụng Và Cách Tải Vào Máy Tính Của Bạn

Lời mở đầu

Trong thời đại số hoá, dữ liệu đã trở thành nguồn tài nguyên quan trọng nhất của mọi doanh nghiệp. Tuy nhiên, để khai thác giá trị thực sự từ dữ liệu, người dùng không chỉ cần thu thập mà còn phải xử lý, làm sạch, chuyển đổi và tích hợp chúng một cách hiệu quả. Đối với những người làm việc với Microsoft Excel – một trong những công cụ phổ biến nhất để phân tích dữ liệu – Power Query đã ra đời như một “cây đao” mạnh mẽ, giúp biến các công việc ETL (Extract, Transform, Load) trở nên đơn giản, trực quan và tự động hoá.

Bài viết này sẽ đi sâu vào khám phá Microsoft Power Query cho Excel, cung cấp cho bạn một cái nhìn toàn diện từ khái niệm cơ bản, các tính năng nổi bật, quy trình làm việc, đến các mẹo nâng cao giúp tối ưu hoá quy trình làm việc hàng ngày. Dù bạn là người mới bắt đầu hay một chuyên gia dữ liệu, hy vọng nội dung dưới đây sẽ giúp bạn nắm bắt và khai thác tối đa tiềm năng của Power Query.

Có thể bạn quan tâm: Hướng Dẫn Chi Tiết Cách Merge Excel Files Into One: Kỹ Thuật, Công Cụ Và Mẹo Hay

1. Power Query là gì? (Giới thiệu tổng quan)

Power Query, còn được gọi là Get & Transform trong Excel, là một công cụ ETL (Extract, Transform, Load) tích hợp sẵn trong Excel (từ Excel 2026 trở lên) và trong Power BI Desktop. Nó cho phép người dùng:

• Kết nối tới nhiều nguồn dữ liệu khác nhau (tệp CSV, Excel, SQL Server, Web API, SharePoint, Azure, v.v.).
• Trực quan hóa quá trình làm sạch và biến đổi dữ liệu bằng giao diện kéo‑thả và các bước thao tác.
• Lưu trữ các bước biến đổi dưới dạng script M (ngôn ngữ Power Query) để tái sử dụng và chia sẻ.
• Tự động hoá quá trình tải dữ liệu mới mà không cần viết mã phức tạp.

Power Query thực chất là một ngôn ngữ lập trình (M) và một trình chỉnh sửa giao diện (Power Query Editor). Khi người dùng thực hiện các thao tác trong giao diện, Power Query tự động tạo ra mã M tương ứng, cho phép người dùng xem và chỉnh sửa nếu cần.

Có thể bạn quan tâm: Cách Sử Dụng Hàm Max Kèm Điều Kiện Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Lý do nên sử dụng Power Query trong Excel

2.1. Tiết kiệm thời gian và giảm lỗi thủ công

Trước khi có Power Query, việc hợp nhất dữ liệu từ nhiều file, loại bỏ dòng trùng lặp, hay chuyển đổi định dạng thường đòi hỏi viết công thức phức tạp, sao chép‑dán hoặc dùng macro VBA. Mỗi thao tác đều tiềm ẩn nguy cơ lỗi. Power Query cho phép thực hiện mọi công việc này chỉ với vài cú click, và các bước được ghi lại để tái sử dụng.

2.2. Tính tái sử dụng và tự động hoá

Sau khi thiết lập một quy trình ETL, người dùng chỉ cần Refresh để lấy dữ liệu mới, Power Query sẽ tự động áp dụng tất cả các bước đã định nghĩa. Điều này rất hữu ích cho các báo cáo định kỳ (hàng ngày, tuần, tháng).

2.3. Hỗ trợ nhiều nguồn dữ liệu

Power Query hỗ trợ hơn 100 nguồn dữ liệu, từ file cục bộ, cơ sở dữ liệu quan hệ, dịch vụ đám mây, tới các API REST. Điều này giúp người dùng không còn bị giới hạn trong việc nhập dữ liệu chỉ từ một nguồn duy nhất.

2.4. Tích hợp sâu với Power BI và các công cụ Microsoft

Mã M và các truy vấn Power Query được chia sẻ một cách liền mạch giữa Excel và Power BI, cho phép người dùng phát triển mô hình dữ liệu trong Excel và sau đó mở rộng sang Power BI mà không cần tái tạo lại quy trình.

3. Cài đặt và truy cập Power Query trong Excel

Lưu ý: Đối với người dùng Windows 10/11, đảm bảo cập nhật Office để nhận các tính năng mới nhất và sửa lỗi bảo mật.

4. Quy trình làm việc cơ bản với Power Query

4.1. Bước 1: Kết nối tới nguồn dữ liệu (Extract)

1. Mở Power Query Editor: Vào Data → Get Data và chọn nguồn (ví dụ: From File → From Workbook).
2. Chọn file và bảng dữ liệu: Duyệt tới file Excel, chọn bảng (Table) hoặc phạm vi (Range) muốn nhập.
3. Xem trước dữ liệu: Power Query sẽ hiển thị bản xem trước, cho phép bạn kiểm tra nhanh.

4.2. Bước 2: Làm sạch và biến đổi dữ liệu (Transform)

Trong Power Query Editor, bạn sẽ thấy các Applied Steps (các bước đã thực hiện) ở phía bên phải. Các thao tác phổ biến bao gồm:

Mỗi thao tác sẽ tự động tạo một step mới trong danh sách Applied Steps, cho phép bạn quay lại, chỉnh sửa hoặc xóa bất kỳ bước nào.

4.3. Bước 3: Tải dữ liệu vào Excel (Load)

Sau khi hoàn thành các bước biến đổi:

1. Close & Load: Nhấn Home → Close & Load để tải dữ liệu vào một sheet mới hoặc vào Data Model (cho Power Pivot).
2. Load To…: Nếu muốn tải vào bảng, PivotTable, hoặc chỉ tạo kết nối, chọn Load To… và cấu hình tùy chọn.

4.4. Bước 4: Làm mới dữ liệu (Refresh)

Khi nguồn dữ liệu thay đổi (ví dụ: file CSV mới được cập nhật), chỉ cần Refresh All (Data → Refresh All) và Power Query sẽ tự động áp dụng tất cả các bước đã định nghĩa để cập nhật bảng trong Excel.

5. Các tính năng nâng cao của Power Query

5.1. Sử dụng ngôn ngữ M

Có thể bạn quan tâm: Microsoft Excel Training Online Free: Hướng Dẫn Toàn Diện Để Thành Thạo Bảng Tính

M là ngôn ngữ lập trình chức năng được thiết kế cho Power Query. Khi thực hiện các thao tác trong giao diện, Power Query tạo ra mã M tương ứng. Người dùng có thể:

• Xem mã M: Trong Power Query Editor, vào View → Advanced Editor.
• Chỉnh sửa mã M: Thêm, xóa hoặc thay đổi các hàm M để thực hiện các biến đổi phức tạp (ví dụ: hàm List.Generate, Table.AddColumn với công thức tùy chỉnh).
• Tái sử dụng mã M: Lưu script M dưới dạng .pq hoặc .txt và chia sẻ cho đồng nghiệp.

Ví dụ: Thêm cột tính ngày làm việc

let Source = Excel.CurrentWorkbook(){Name="Sales"}Content, #"Changed Type" = Table.TransformColumnTypes(Source,{{"OrderDate", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "IsWorkday", each if Date.DayOfWeek(OrderDate, Day.Monday) < 5 then true else false)
in #"Added Custom"

5.2. Tham chiếu và nhân bản truy vấn (Reference vs Duplicate)

• Duplicate: Tạo một bản sao hoàn toàn của truy vấn, bao gồm toàn bộ các bước. Thích hợp khi muốn có một phiên bản độc lập.
• Reference: Tạo truy vấn mới dựa trên kết quả của một truy vấn hiện có, chia sẻ các bước chung. Giúp giảm thiểu việc duy trì nhiều bản sao giống nhau.

5.3. Tham số hoá (Parameter) và hàm tùy chỉnh

Bạn có thể tạo Parameter để cho phép người dùng nhập giá trị (ví dụ: ngày bắt đầu, tên file) mà không cần chỉnh sửa mã. Các bước:

1. Manage Parameters → New Parameter.
2. Đặt giá trị mặc định, kiểu dữ liệu.
3. Sử dụng tham số trong các bước (ví dụ: Source = Csv.Document(File.Contents(ParameterFilePath), …)).

Hàm tùy chỉnh: Bạn có thể viết hàm trong M để tái sử dụng logic phức tạp. Ví dụ, hàm để chuẩn hoá số điện thoại:

let NormalizePhone = (phone as text) as text => let digits = Text.Select(phone, {"0".."9"}), result = if Text.Length(digits) = 10 then Text.Start(digits, 3) & "-" & Text.Middle(digits,3,3) & "-" & Text.End(digits,4) else null in result
in NormalizePhone

Sau đó, trong Add Custom Column, gọi hàm: NormalizePhone(PhoneNumber).

5.4. Kết nối tới API và dữ liệu Web

Power Query hỗ trợ Web.Contents để lấy dữ liệu JSON, XML hoặc CSV từ API. Ví dụ, lấy dữ liệu thời tiết từ OpenWeatherMap:

let apiKey = "YOUR_API_KEY", city = "Hanoi", url = "https://api.openweathermap.org/data/2.5/weather?q=" & city & "&appid=" & apiKey, Source = Json.Document(Web.Contents(url)), Main = Sourcemain, TemperatureK = Maintemp, TemperatureC = TemperatureK - 273.15
in TemperatureC

5.5. Tích hợp Power Query với Power Pivot và Power BI

• Load to Data Model: Khi tải dữ liệu, chọn Only Create Connection và tick Add this data to the Data Model. Dữ liệu sẽ được lưu trong Power Pivot, cho phép tạo các mối quan hệ, tính toán DAX.
• Export to Power BI: Mở Power BI Desktop → Get Data → Excel → chọn file chứa truy vấn. Power BI sẽ tự động nhập các truy vấn Power Query và giữ nguyên các bước biến đổi.

6. Các trường hợp sử dụng thực tiễn

6.1. Hợp nhất báo cáo bán hàng từ nhiều chi nhánh

Mỗi chi nhánh gửi file Excel hàng tuần với cấu trúc khác nhau (cột “Date” vs “OrderDate”). Power Query cho phép:

1. Import các file từ một thư mục (Folder → Combine).
2. Standardize tên cột bằng Table.RenameColumns.
3. Convert định dạng ngày.
4. Aggregate tổng doanh thu theo tháng.

Kết quả: Một bảng duy nhất, luôn cập nhật khi có file mới.

6.2. Xử lý dữ liệu log từ hệ thống

Log server thường xuất ra file CSV với hàng nghìn dòng. Power Query:

• Import file CSV.
• Filter các dòng lỗi (Level = "Error").
• Extract thông tin thời gian bằng DateTime.FromText.
• Group By để đếm số lỗi theo ngày.

Sau khi thiết lập, chỉ cần Refresh để có báo cáo lỗi mới nhất.

6.3. Tự động hoá báo cáo tài chính hàng tháng

• Parameter: Tháng và năm cần báo cáo.
• Query: Lấy dữ liệu từ SQL Server (thông qua Sql.Database), lọc theo tháng.
• Transform: Tính tổng, tỷ lệ, và tạo bảng Pivot.
• Load: Đưa dữ liệu vào Excel, dùng PivotTable để tạo báo cáo.

Nhờ Power Query, quy trình báo cáo giảm từ 2-3 giờ xuống còn vài phút.

7. Mẹo và thủ thuật để tối ưu Power Query

8. Giải quyết các vấn đề thường gặp

8.1. Query folding không hoạt động

• Nguyên nhân: Thực hiện bước không hỗ trợ folding (ví dụ: Table.AddIndexColumn), hoặc dùng nguồn không hỗ trợ (CSV, Excel).
• Giải pháp: Đặt các bước filter, group, join ngay sau khi import, trước khi thực hiện các bước không hỗ trợ. Kiểm tra bằng cách chuột phải vào bước → View Native Query.

8.2. Lỗi “DataFormat.Error: We couldn’t convert to Number”

• Nguyên nhân: Cột chứa giá trị không phải số (ví dụ: ký tự, dấu phẩy thập phân không đồng nhất).
• Giải pháp: Trước khi đổi kiểu, dùng Replace Values để chuẩn hoá (thay , bằng .), hoặc Add Column → Conditional Column để xử lý ngoại lệ.

8.3. Thời gian refresh quá lâu

• Nguyên nhân: Dữ liệu quá lớn, không dùng folding, hoặc có quá nhiều bước phức tạp.
• Giải pháp:
• Kiểm tra Query Diagnostics.
• Sử dụng Table.Buffer cho các bảng được join nhiều lần.
• Loại bỏ các cột không cần thiết ngay sau khi import.
• Nếu có thể, chuyển sang nguồn dữ liệu hỗ trợ folding (SQL Server, Azure).

8.4. Không thể kết nối tới nguồn dữ liệu Web

• Nguyên nhân: Yêu cầu authentication, chứng chỉ SSL không hợp lệ, hoặc giới hạn IP.
• Giải pháp:
• Sử dụng Web.Contents với Headers và Query thích hợp.
• Kiểm tra proxy và firewall.
• Đăng nhập vào dịch vụ (OAuth) nếu cần.

9. So sánh Power Query với các công cụ ETL khác

Power Query là lựa chọn lý tưởng cho người dùng Excel muốn tự động hoá quy trình mà không rời khỏi môi trường quen thuộc. Đối với các dự án phức tạp, liên quan tới Big Data, có thể cân nhắc kết hợp Power Query với Power BI hoặc các công cụ chuyên dụng hơn.

10. Học tập và tài nguyên nâng cao

1. Microsoft Learn – “Get & Transform Data in Excel” (cung cấp hướng dẫn tương tác).
2. Power Query Documentation – Trang chính thức của Microsoft, bao gồm danh sách hàm M.
3. YouTube Channels:
4. MyExcelOnline – Series “Power Query Basics”.
5. Leila Gharani – Video thực hành thực tế.
6. Guy in a Cube – Kết hợp Power Query và Power BI.
7. Sách:
8. M Is for Data Monkey – Ken Puls & Miguel Escobar.
9. Power Query for Power BI and Excel – Chris Webb.
10. Cộng đồng:
11. Power Query Forum (Microsoft Tech Community).
12. r/excel và r/powerquery trên Reddit.
13. Stack Overflow – tag powerquery.

11. Kết luận

Microsoft Power Query đã biến Excel trở thành một công cụ ETL mạnh mẽ, cho phép người dùng không chỉ nhập dữ liệu mà còn thực hiện các thao tác làm sạch, biến đổi và tích hợp một cách tự động và có thể tái sử dụng. Với giao diện trực quan, khả năng kết nối đa dạng và ngôn ngữ M linh hoạt, Power Query đáp ứng nhu cầu của cả người mới bắt đầu và các chuyên gia dữ liệu.

Việc nắm vững Power Query sẽ giúp:

• Tiết kiệm thời gian đáng kể trong việc chuẩn bị dữ liệu.
• Giảm lỗi do thao tác thủ công.
• Tăng tính nhất quán trong báo cáo định kỳ.
• Mở rộng khả năng phân tích bằng cách tích hợp sâu với Power Pivot, Power BI và các công cụ đám mây.

Hãy bắt đầu khám phá Power Query ngay hôm nay, tạo ra những quy trình dữ liệu thông minh, tự động hoá và đưa doanh nghiệp của bạn tiến lên một bước dài hơn trong hành trình chuyển đổi số.

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.