Power Query Thực Chiến: 7 Bài Toán Thực Tế Từ Gộp File Đến ETL Tự Động

Power Query là công cụ ETL (Extract - Transform - Load) tích hợp sẵn trong Excel và Power BI. Nhưng nhiều người chỉ biết dùng nó ở mức cơ bản. Bài viết này sẽ giải quyết 7 bài toán thực tế mà bất kỳ ai làm việc với dữ liệu đều gặp.

Bài 1: Gộp Tất Cả File Excel Trong 1 Thư Mục #

Tình huống: Bạn có 12 file Excel (mỗi file 1 tháng) trong 1 folder, cần gộp tất cả vào 1 bảng duy nhất.

Các bước thực hiện #

• Data → Get Data → From File → From Folder

• Chọn thư mục chứa các file

• Click Combine → Combine & Transform Data

• Chọn sheet cần lấy → OK

• Power Query tự động gộp tất cả file + thêm cột "Source.Name" cho biết dữ liệu từ file nào

// M Language: Gộp file từ folder
let
    Source = Folder.Files("C:\Data\BaoCao2024"),
    FilterExcel = Table.SelectRows(Source, each [Extension] = ".xlsx"),
    ExtractData = Table.AddColumn(FilterExcel, "Data", each 
        Excel.Workbook([Content]){[Item="Sheet1"]}[Data]),
    ExpandData = Table.ExpandTableColumn(ExtractData, "Data", 
        {"Col1", "Col2", "Col3", "Col4"}),
    RemoveOther = Table.SelectColumns(ExpandData, {"Name", "Col1", "Col2", "Col3", "Col4"})
in
    RemoveOther

Mẹo: Khi thêm file mới vào folder, chỉ cần Refresh query — dữ liệu mới tự động được gộp vào!

Bài 2: Unpivot Bảng Chéo Thành Bảng Dọc #

Tình huống: Bảng doanh thu có dạng ngang (Sản phẩm × Tháng) nhưng Pivot Table cần dạng dọc (Sản phẩm, Tháng, Giá trị).

// Chọn cột cố định (Sản phẩm) → Transform → Unpivot Other Columns
// Kết quả: 3 cột: Sản phẩm | Attribute (Tháng) | Value (Doanh thu)

let
    Source = Excel.CurrentWorkbook(){[Name="BangCheo"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"SanPham"}, "Thang", "DoanhThu"),
    ChangeType = Table.TransformColumnTypes(Unpivot, {{"DoanhThu", type number}})
in
    ChangeType

Bài 3: Merge Dữ Liệu Từ Nhiều Nguồn #

Tình huống: Bảng Đơn hàng (mã KH) cần ghép thêm Tên KH, Địa chỉ từ bảng Khách hàng — giống VLOOKUP nhưng mạnh hơn.

// Home → Merge Queries
// Chọn bảng DonHang, cột MaKH
// Chọn bảng KhachHang, cột MaKH
// Join Kind: Left Outer (giữ tất cả đơn hàng)

// Expand cột KhachHang → chọn TenKH, DiaChi
// Kết quả: bảng DonHang + TenKH + DiaChi

• Left Outer: Giữ tất cả dòng bảng trái (phổ biến nhất)

• Inner: Chỉ giữ dòng khớp ở cả 2 bảng

• Full Outer: Giữ tất cả dòng cả 2 bảng

• Anti Join: Chỉ giữ dòng KHÔNG khớp (tìm dữ liệu thiếu)

Bài 4: Xử Lý Ngày Tháng Lỗi #

Tình huống: File CSV có ngày dạng text "15-Mar-24" hoặc lẫn lộn DD/MM và MM/DD.

// Đổi locale khi import
let
    Source = Csv.Document(File.Contents("data.csv"), null, ",", null, 1252),
    // Ép kiểu ngày với locale Việt Nam
    ChangeType = Table.TransformColumnTypes(Source, {{"Ngay", type date}}, "vi-VN"),
    // Hoặc parse thủ công
    ParseDate = Table.AddColumn(Source, "NgayChuẩn", each 
        Date.From(Text.Middle([Ngay], 3, 2) & "/" & Text.Start([Ngay], 2) & "/" & Text.End([Ngay], 4)))
in
    ParseDate

Bài 5: Tách Cột Phức Tạp #

Tình huống: Cột "Họ tên - Chức vụ - Phòng ban" cần tách thành 3 cột riêng.

// Transform → Split Column → By Delimiter (" - ")
// Hoặc bằng M Language:
Table.AddColumn(Source, "HoTen", each Text.BeforeDelimiter([FullInfo], " - "))

• Split by Delimiter: Tách theo ký tự phân cách

• Split by Position: Tách theo vị trí cố định

• Split by Transition: Tách khi kiểu ký tự thay đổi (chữ→số)

• Extract: Trích xuất Text Before/After/Between delimiter

Bài 6: Parameter Query — Truy Vấn Động #

Tình huống: Muốn thay đổi điều kiện lọc (tháng, năm, phòng ban) mà không cần sửa query.

// 1. Tạo bảng Parameter trong Excel: A1=TenParam, B1=GiaTri
//    A2="Thang", B2=3
//    A3="Nam", B3=2024

// 2. Load bảng Parameter vào Power Query
// 3. Tạo function lấy giá trị parameter:
let
    ParamTable = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    GetParam = (name as text) => 
        Table.SelectRows(ParamTable, each [TenParam] = name){0}[GiaTri]
in
    GetParam

// 4. Dùng trong query chính:
// Table.SelectRows(Source, each [Thang] = GetParam("Thang"))

Bài 7: ETL Tự Động — Refresh Theo Lịch #

Sau khi xây dựng toàn bộ pipeline Power Query, bạn cần tự động hóa việc refresh.

• Trong Excel: Data → Connections → Properties → Refresh Every X minutes

• Trong Power BI Service: Schedule Refresh (tối đa 8 lần/ngày Free, 48 lần Pro)

• Dùng VBA: ActiveWorkbook.RefreshAll chạy khi mở file

• Dùng Task Scheduler + VBScript để chạy file Excel + refresh + lưu + đóng tự động

' VBA: Auto refresh khi mở + lưu kết quả
Private Sub Workbook_Open()
    ThisWorkbook.RefreshAll
    Application.Wait Now + TimeValue("00:00:30") ' Chờ 30s
    ThisWorkbook.Save
End Sub

Kết Luận #

Power Query giải quyết 90% bài toán xử lý dữ liệu mà trước đây phải viết VBA hoặc làm thủ công: gộp file, unpivot, merge, clean data, ETL tự động. Quan trọng nhất: mọi bước đều được ghi lại và có thể refresh bằng 1 click.

Khám phá thêm Power Query tại Trà Đá Data! 🍵