Excel Power Query 2026: ETL cho Data Analyst và Câu hỏi Phỏng vấn

Excel Power Query thay đổi cách data analyst xử lý quy trình ETL (Extract, Transform, Load) trực tiếp trong Excel. Khác với copy-paste thủ công hay macro VBA phức tạp, Power Query cung cấp giao diện trực quan được hỗ trợ bởi ngôn ngữ M, cho phép các phép biến đổi dữ liệu có thể lặp lại và refresh chỉ với một cú nhấp chuột.

Vấn đề Power Query giải quyết cho Data Analyst

Data analyst dành nhiều thời gian cho việc chuẩn bị dữ liệu: hợp nhất file từ nhiều nguồn khác nhau, làm sạch các định dạng không nhất quán, lọc các hàng không liên quan và định hình lại bảng để phân tích. Power Query giải quyết những nhiệm vụ này thông qua query editor ghi lại từng bước biến đổi. Khi dữ liệu nguồn thay đổi, toàn bộ pipeline tự động thực thi lại.

Quy trình làm việc tuân theo ba giai đoạn: kết nối đến nguồn dữ liệu, áp dụng các phép biến đổi và tải kết quả vào bảng Excel hoặc data model. Mỗi bước được ghi lại trong thanh công thức sử dụng cú pháp ngôn ngữ M, có thể chỉnh sửa trực tiếp cho các tình huống nâng cao.

Cách tiếp cận này khác với công thức Excel truyền thống. Trong khi công thức tính toán lại theo từng ô, Power Query hoạt động trên toàn bộ bảng trước khi đến worksheet. Một query hợp nhất 50 file CSV, loại bỏ trùng lặp và unpivot các cột chạy một lần và tạo ra bảng sạch, thay vì xây dựng các công thức lồng nhau phức tạp làm chậm workbook.

Kết nối đến Nguồn dữ liệu với Get Data

Power Query hỗ trợ kết nối đến file (CSV, Excel, JSON, XML), database (SQL Server, MySQL, PostgreSQL, Oracle), dịch vụ cloud (SharePoint, Azure, Salesforce) và trang web. Loại kết nối xác định các tùy chọn xác thực và nhập xuất hiện.

// Common data sources in Power Query
Data > Get Data > From File > From CSV
Data > Get Data > From Database > From SQL Server Database
Data > Get Data > From Other Sources > From Web
Data > Get Data > From Folder (multiple files)

Tùy chọn "From Folder" đặc biệt hữu ích để hợp nhất nhiều file. Thay vì nhập từng file riêng lẻ, Power Query quét một thư mục, liệt kê tất cả các file phù hợp và kết hợp chúng thành một query duy nhất. Thêm file mới vào thư mục tự động đưa nó vào lần refresh tiếp theo.

Khi kết nối đến database SQL, Power Query có thể đẩy logic biến đổi đến server thông qua query folding. Các bộ lọc và lựa chọn cột được dịch thành các mệnh đề SQL WHERE và SELECT, giảm dữ liệu truyền đến Excel. Thanh công thức hiển thị tùy chọn "View Native Query" khi folding đang hoạt động.

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

Query Editor cung cấp các lệnh ribbon cho các thao tác phổ biến, nhưng hiểu mã M cơ bản sẽ hữu ích khi cần tùy chỉnh. Mỗi phép biến đổi thêm một bước vào bảng "Applied Steps", tạo ra một chuỗi có thể kiểm tra.

Lọc và Sắp xếp Hàng

Lọc loại bỏ các hàng không đáp ứng tiêu chí. Menu thả xuống của header cột cung cấp bộ lọc nhanh, trong khi hộp thoại "Filter Rows" hỗ trợ các điều kiện phức tạp với logic AND/OR.

// FilteredRows step in M language
let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilteredRows = Table.SelectRows(Source, each [Region] = "EMEA" and [Amount] > 1000)
in
    FilteredRows

Hàm Table.SelectRows nhận một bảng và một điều kiện. Từ khóa each tạo một hàm trong đó _ đại diện cho hàng hiện tại, và truy cập trường sử dụng ký hiệu ngoặc vuông [Region]. Nhiều điều kiện kết hợp với toán tử and hoặc or.

Xóa và Đổi tên Cột

Nguồn dữ liệu thường bao gồm các cột không cần thiết cho phân tích. Xóa chúng sớm giúp giảm sử dụng bộ nhớ và đơn giản hóa các bước tiếp theo.

// Remove columns, then rename remaining ones
let
    Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],
    RemovedColumns = Table.RemoveColumns(Source, {"TempID", "InternalNotes", "Debug"}),
    RenamedColumns = Table.RenameColumns(RemovedColumns, {{"Cust_Name", "CustomerName"}, {"Amt", "Amount"}})
in
    RenamedColumns

Danh sách cột sử dụng dấu ngoặc nhọn {} cho nhiều mục. Đổi tên sử dụng danh sách các cặp, trong đó mỗi cặp chứa tên cũ và tên mới. Quy ước đặt tên nhất quán giữa các query giúp việc kết hợp dataset dễ dàng hơn.

Tách và Hợp nhất Cột

Cột văn bản thường cần phân tích cú pháp. Cột "FullName" có thể cần tách thành tên và họ, hoặc các cột ngày và giờ riêng biệt có thể cần hợp nhất.

// Split FullName by delimiter into two columns
let
    Source = Excel.CurrentWorkbook(){[Name="Contacts"]}[Content],
    SplitColumn = Table.SplitColumn(Source, "FullName", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"FirstName", "LastName"})
in
    SplitColumn

Hàm Splitter.SplitTextByDelimiter xử lý logic phân tích cú pháp. Đối với các mẫu phức tạp hơn, Splitter.SplitTextByEachDelimiter hoặc Splitter.SplitTextByPositions cung cấp khả năng kiểm soát bổ sung. Khi số lượng cột kết quả thay đổi, Power Query tạo cột động.

Chuyển đổi Kiểu dữ liệu và Chất lượng Dữ liệu

Power Query suy luận kiểu cột khi nhập, nhưng gán kiểu rõ ràng giúp phát hiện lỗi sớm. Cột văn bản chứa ID số nên giữ nguyên là văn bản nếu các số 0 đầu quan trọng. Cột ngày được nhập dưới dạng văn bản gây ra vấn đề sắp xếp.

// Explicit type assignments
let
    Source = Csv.Document(File.Contents("C:\\Data\\transactions.csv")),
    TypedColumns = Table.TransformColumnTypes(Source, {
        {"TransactionID", type text},
        {"Date", type date},
        {"Amount", type number},
        {"IsProcessed", type logical}
    })
in
    TypedColumns

Từ khóa type chỉ định kiểu đích. Các kiểu có sẵn bao gồm text, number, date, datetime, datetimezone, time, duration, logical và binary. Lỗi kiểu xuất hiện dưới dạng giá trị "Error" trong ô, làm cho vấn đề chất lượng dữ liệu hiển thị trước khi phân tích.

Xử lý giá trị null yêu cầu logic rõ ràng. Hàm Table.ReplaceValue thay thế null bằng giá trị mặc định, trong khi Table.SelectRows với [Column] <> null lọc chúng ra.

Nhóm và Tổng hợp với Group By

Tổng hợp dữ liệu theo danh mục là yêu cầu thường xuyên. Phép biến đổi "Group By" thu gọn các hàng có cùng giá trị khóa và áp dụng các hàm tổng hợp.

// Group sales by region and year, calculate sum and count
let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Grouped = Table.Group(Source, {"Region", "Year"}, {
        {"TotalSales", each List.Sum([Amount]), type number},
        {"OrderCount", each Table.RowCount(_), Int64.Type},
        {"AvgOrderValue", each List.Average([Amount]), type number}
    })
in
    Grouped

Các cột nhóm xuất hiện đầu tiên, tiếp theo là định nghĩa tổng hợp. Mỗi tổng hợp chỉ định tên cột mới, hàm tổng hợp và kiểu kết quả tùy chọn. Từ khóa each đại diện cho bảng con cho mỗi nhóm, cho phép bất kỳ hàm bảng hoặc danh sách nào.

Tổng hợp lồng nhau cho phép tính toán như "phần trăm của tổng nhóm" bằng cách tham chiếu cả giá trị hàng và tổng hợp nhóm trong bước tiếp theo.

Pivoting và Unpivoting để Định hình lại Dữ liệu

Pivoting chuyển đổi giá trị hàng thành cột, tạo bố cục crosstab. Unpivoting làm ngược lại, chuyển đổi cột thành hàng cho cấu trúc chuẩn hóa.

// Unpivot month columns into rows
let
    Source = Excel.CurrentWorkbook(){[Name="MonthlySales"]}[Content],
    // Original: Product, Jan, Feb, Mar, Apr columns
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Product"}, "Month", "Sales")
    // Result: Product, Month, Sales columns
in
    Unpivoted

Hàm Table.UnpivotOtherColumns giữ nguyên các cột được chỉ định và unpivot phần còn lại. Điều này an toàn hơn so với liệt kê tất cả các cột để unpivot, vì thêm cột tháng mới tự động bao gồm nó. Hai tham số cuối đặt tên cho cột thuộc tính ("Month") và cột giá trị ("Sales").

Pivoting sử dụng Table.Pivot với hàm tổng hợp cho các trường hợp có nhiều giá trị tồn tại cho cùng tổ hợp hàng-cột:

// Pivot sales by region
let
    Source = Excel.CurrentWorkbook(){[Name="DetailedSales"]}[Content],
    Pivoted = Table.Pivot(Source, List.Distinct(Source[Region]), "Region", "Amount", List.Sum)
in
    Pivoted

Hợp nhất Query với Merge và Append

Kết hợp dữ liệu từ nhiều nguồn là nơi Power Query giảm công sức thủ công. Merge thực hiện join giữa hai bảng dựa trên các cột khớp. Append xếp chồng các bảng theo chiều dọc.

// Left join: Orders with Customer details
let
    Orders = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    Customers = Excel.CurrentWorkbook(){[Name="Customers"]}[Content],
    Merged = Table.NestedJoin(Orders, {"CustomerID"}, Customers, {"ID"}, "CustomerDetails", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "CustomerDetails", {"Name", "Email"})
in
    Expanded

Hàm Table.NestedJoin tạo cột bảng lồng nhau chứa các hàng khớp. Hàm Table.ExpandTableColumn sau đó làm phẳng cấu trúc lồng nhau thành các cột thông thường. Các loại join bao gồm Inner, LeftOuter, RightOuter, FullOuter, LeftAnti và RightAnti.

Append với Table.Combine yêu cầu tên cột khớp. Khi schema khác nhau, Table.SelectColumns trên mỗi nguồn trước khi kết hợp đảm bảo tính nhất quán:

// Append two sales tables with consistent columns
let
    Sales2024 = Table.SelectColumns(Source2024, {"Date", "Product", "Amount"}),
    Sales2025 = Table.SelectColumns(Source2025, {"Date", "Product", "Amount"}),
    Combined = Table.Combine({Sales2024, Sales2025})
in
    Combined

Cột Tùy chỉnh và Logic Điều kiện

Tính năng "Add Column > Custom Column" cho phép các trường tính toán sử dụng biểu thức M. Logic điều kiện sử dụng cú pháp if-then-else.

// Add a calculated column with conditional logic
let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    AddedColumn = Table.AddColumn(Source, "OrderCategory", each 
        if [Amount] >= 10000 then "Enterprise"
        else if [Amount] >= 1000 then "Business"
        else "Consumer",
        type text
    )
in
    AddedColumn

Biểu thức if phải bao gồm cả nhánh then và else. Các điều kiện lồng nhau nối với else if. Tham số cuối chỉ định kiểu cột, cải thiện hiệu suất và ngăn vấn đề suy luận kiểu.

Đối với các phép biến đổi phức tạp, các hàm helper được định nghĩa trong khối let giữ mã dễ đọc:

let
    // Helper function for fiscal quarter
    GetFiscalQuarter = (date as date) as text =>
        let
            month = Date.Month(date),
            fiscalQ = if month >= 4 and month <= 6 then "Q1"
                     else if month >= 7 and month <= 9 then "Q2"
                     else if month >= 10 and month <= 12 then "Q3"
                     else "Q4"
        in
            fiscalQ,
    
    Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],
    AddedQuarter = Table.AddColumn(Source, "FiscalQuarter", each GetFiscalQuarter([Date]), type text)
in
    AddedQuarter

Xử lý Lỗi trong Power Query

Lỗi biến đổi xuất hiện dưới dạng giá trị "Error" trong ô thay vì làm hỏng toàn bộ query. Cấu trúc try-otherwise xử lý lỗi một cách duyên dáng:

// Handle potential division errors
let
    Source = Excel.CurrentWorkbook(){[Name="Metrics"]}[Content],
    AddedRatio = Table.AddColumn(Source, "Ratio", each 
        try [Value1] / [Value2] otherwise null,
        type number
    )
in
    AddedRatio

Từ khóa try thử biểu thức và trả về một record với các trường HasError và Value. Mệnh đề otherwise cung cấp giá trị dự phòng khi HasError là true. Để kiểm soát nhiều hơn, truy cập chi tiết lỗi với try Expression:

// Capture error details
let
    result = try SomeRiskyFunction(),
    output = if result[HasError] then "Error: " & result[Error][Message] else result[Value]
in
    output

Câu hỏi Phỏng vấn Power Query cho Data Analyst

Nhà tuyển dụng đánh giá cả kỹ năng thực hành và hiểu biết về thời điểm Power Query phù hợp với quy trình làm việc. Những câu hỏi này xuất hiện thường xuyên trong phỏng vấn data analyst.

Query folding là gì và tại sao nó quan trọng?

Query folding dịch các phép biến đổi Power Query thành query native cho nguồn dữ liệu. Khi kết nối đến SQL Server, bước lọc trở thành mệnh đề WHERE được thực thi trên server, giảm truyền tải mạng. Không phải tất cả các phép biến đổi đều fold được: các hàm M tùy chỉnh, một số thao tác ngày và các hoạt động sau bước không fold sẽ phá vỡ chuỗi. Kiểm tra trạng thái folding bằng cách nhấp chuột phải vào bước và tìm "View Native Query."

Power Query khác với công thức Excel như thế nào cho biến đổi dữ liệu?

Công thức Excel hoạt động theo từng ô trong worksheet và tính toán lại mỗi khi có thay đổi. Power Query hoạt động trên bảng trước khi chúng đến worksheet, xử lý dữ liệu hàng loạt trong quá trình refresh. Đối với các phép biến đổi như unpivoting, loại bỏ trùng lặp hoặc hợp nhất file, Power Query diễn đạt logic trực tiếp hơn so với INDEX-MATCH lồng nhau hoặc cột helper.

Khi nào nên chọn Power Query thay vì Power BI cho ETL?

Power Query trong Excel phù hợp với các tình huống mà analyst cần dữ liệu đã biến đổi ở dạng spreadsheet cho phân tích ad-hoc, pivot table hoặc chia sẻ với người dùng không có quyền truy cập Power BI. Power BI cung cấp trực quan hóa phong phú hơn, dung lượng dữ liệu lớn hơn và các tính năng chia sẻ enterprise. Mã M tương tự hoạt động ở cả hai công cụ, nên các query được phát triển trong Excel có thể di chuyển sang Power BI Desktop mà không cần viết lại.

Làm thế nào để xử lý không khớp kiểu dữ liệu khi append bảng?

Đặt kiểu rõ ràng trên mỗi bảng nguồn trước khi kết hợp với Table.Combine. Nếu một cột là văn bản ở nguồn này và số ở nguồn khác, append sẽ thất bại hoặc tạo ra lỗi. Sử dụng Table.TransformColumnTypes trên cả hai nguồn để đảm bảo kiểu nhất quán. Mẫu try-otherwise xử lý các trường hợp cạnh khi chuyển đổi thất bại cho các giá trị cụ thể.

Giải thích sự khác biệt giữa Merge và Append trong Power Query.

Merge thực hiện join ngang dựa trên các cột khóa khớp, tương tự SQL JOIN. Append thực hiện union dọc các hàng từ nhiều bảng, tương tự SQL UNION ALL. Merge yêu cầu ít nhất một cột chung để khớp. Append yêu cầu các cột có cùng tên để căn chỉnh đúng.

Để chuẩn bị sâu hơn về các khái niệm SQL bổ sung cho kỹ năng Power Query, module SQL window functions bao gồm các mẫu xếp hạng và tổng hợp, trong khi module SQL subqueries và CTEs đề cập đến các kỹ thuật cấu trúc query.

Tùy chọn Loading và Chiến lược Refresh

Sau các phép biến đổi, nút "Close & Load" cung cấp các lựa chọn loading: load vào bảng worksheet, chỉ load vào data model (Power Pivot), hoặc tạo query connection-only. Query connection-only phục vụ như các bước staging cho các query khác mà không chiếm không gian worksheet.

Hành vi refresh phụ thuộc vào đích load. Bảng worksheet refresh với nút "Refresh All" hoặc có thể được đặt để refresh khi mở file. Bảng data model tham gia vào chu kỳ refresh data model của workbook. Đối với các query kết nối đến database bên ngoài, lời nhắc thông tin đăng nhập có thể xuất hiện khi refresh trừ khi thông tin đăng nhập đã lưu tồn tại.

Background refresh cho phép workbook vẫn có thể sử dụng trong quá trình loading dữ liệu, nhưng tạo ra phức tạp khi các công thức downstream phụ thuộc vào dữ liệu đã refresh. Tùy chọn "Enable background refresh" trong thuộc tính query kiểm soát hành vi này. Đối với báo cáo quan trọng, tắt background refresh đảm bảo thực thi tuần tự.

Cân nhắc Hiệu suất cho Dataset Lớn

Power Query có thể xử lý hàng triệu hàng, nhưng khả năng phản hồi của workbook phụ thuộc vào cách dữ liệu được load. Loading vào data model thay vì bảng worksheet tránh giới hạn hàng của Excel và cải thiện hiệu suất pivot table. Loại bỏ các cột không cần thiết sớm giảm footprint bộ nhớ.

Đối với các query mất vài phút, "Data preview" trong Query Editor chỉ hiển thị mẫu. Các phép biến đổi áp dụng cho dataset đầy đủ trong quá trình refresh. Lỗi hiển thị trong preview chỉ ra vấn đề, nhưng một số lỗi chỉ xuất hiện với dữ liệu đầy đủ. Chạy test refresh trên tập con xác nhận query trước khi xử lý mọi thứ.

Khi hợp nhất nhiều file, tính năng binary combination của Power Query xử lý file song song. Định nghĩa một hàm biến đổi một file, sau đó gọi nó cho mỗi hàng trong danh sách file, cung cấp nhiều kiểm soát hơn so với cách tiếp cận combine mặc định.

Tài liệu Microsoft về Power Query best practices chi tiết các kỹ thuật tối ưu hóa bao gồm cấu trúc phụ thuộc query và sử dụng buffer.

Power Query cho Pipeline ETL Có thể Lặp lại

Các bước được ghi lại trong Power Query tạo tài liệu về logic biến đổi. Khi yêu cầu thay đổi, sửa đổi một bước tự động cập nhật tất cả các bước downstream. Điều này khác với các thao tác Excel ad-hoc yêu cầu tạo lại từ đầu khi dữ liệu nguồn thay đổi.

Các nhóm có thể chia sẻ query bằng cách xuất kết nối hoặc bằng cách lưu trữ mã M query trong version control. Advanced Editor (View > Advanced Editor) hiển thị script M hoàn chỉnh, có thể được sao chép và dán vào workbook khác. Đối với các tình huống enterprise, Power BI dataflows và Fabric cung cấp quản lý query tập trung.

• Power Query xử lý ETL trong Excel sử dụng các bước biến đổi được ghi lại và có thể lặp lại
• Ngôn ngữ M cơ bản của giao diện trực quan cho phép tùy chỉnh ngoài các lệnh ribbon
• Query folding đẩy bộ lọc và projection đến database nguồn để có hiệu suất tốt hơn
• Merge thực hiện join giữa các bảng, Append xếp chồng bảng theo chiều dọc
• Gán kiểu và xử lý lỗi ngăn chặn vấn đề chất lượng dữ liệu không được nhận thấy
• Query connection-only tạo các bước staging có thể tái sử dụng mà không có đầu ra worksheet
• Câu hỏi phỏng vấn tập trung vào query folding, so sánh công thức và phân biệt merge với append