Power Query Từ A Đến Z: Gộp File, Xử Lý Data Và Automation

Data → Get Data → From File → From Workbook (Excel)
                              → From Text/CSV

Bước 1: Data → Get Data → From File → From Folder
Bước 2: Chọn folder chứa các file báo cáo
Bước 3: Click "Combine & Transform Data"
Bước 4: Chọn sheet name (nếu Excel) → OK
Bước 5: PQ tự gộp tất cả file, thêm cột "Source.Name" để biết dữ liệu từ file nào

// SQL Server
Data → Get Data → From Database → From SQL Server Database
→ Nhập Server name + Database name → OK

// Từ Web (API/HTML table)
Data → Get Data → From Other Sources → From Web
→ Nhập URL → PQ tự detect bảng dữ liệu trong trang

// M language (tự sinh khi thao tác trên UI)
= Table.TransformColumnTypes(Source, {{"NgayBan", type date}, {"SoLuong", Int64.Type}, {"DonGia", type number}})

// Xóa dòng trống
= Table.SelectRows(Source, each [HoTen] <> null and [HoTen] <> "")

// Lọc theo điều kiện
= Table.SelectRows(Source, each [KhuVuc] = "Hà Nội" and [DoanhThu] > 10000000)

// Tách Họ Tên thành Họ + Tên
= Table.SplitColumn(Source, "HoTen", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Ho", "Ten"})

// Dữ liệu gốc: Sản Phẩm | T01 | T02 | T03 | ...
// Sau Unpivot:  Sản Phẩm | Tháng | Doanh Thu

// Chọn cột cố định → Right-click → Unpivot Other Columns
= Table.UnpivotOtherColumns(Source, {"SanPham"}, "Thang", "DoanhThu")

// Giống LEFT JOIN trong SQL
Bước 1: Home → Merge Queries
Bước 2: Chọn bảng chính (Orders) và bảng lookup (Products)
Bước 3: Chọn cột key: Orders[MaSP] ↔ Products[MaSP]
Bước 4: Join Kind: Left Outer
Bước 5: Expand cột vừa merge → chọn cột cần lấy

// M language
= Table.NestedJoin(Orders, {"MaSP"}, Products, {"MaSP"}, "Products", JoinKind.LeftOuter)
= Table.ExpandTableColumn(Source, "Products", {"TenSP", "NhomHang", "DonGia"})

// Tổng doanh thu theo Khu Vực + Tháng
= Table.Group(Source, {"KhuVuc", "Thang"}, {{"TongDoanhThu", each List.Sum([DoanhThu]), type number}, {"SoDon", each Table.RowCount(_), Int64.Type}})

Bước 1: Right-click query trong Queries & Connections pane
Bước 2: Properties → Refresh every 60 minutes
Bước 3: ✅ Enable background refresh
Bước 4: ✅ Refresh data when opening the file

// Thay thế lỗi bằng null
= Table.ReplaceErrorValues(Source, {{"DoanhThu", null}, {"SoLuong", 0}})

// Try-otherwise pattern
= try Number.FromText([GiaTri]) otherwise 0

let
    // Bước 1: Lấy dữ liệu
    Source = Excel.CurrentWorkbook(){[Name="BanHang"]}[Content],
    
    // Bước 2: Đổi kiểu
    ChangedType = Table.TransformColumnTypes(Source, {{"Ngay", type date}}),
    
    // Bước 3: Lọc
    Filtered = Table.SelectRows(ChangedType, each [KhuVuc] = "Hà Nội"),
    
    // Bước 4: Nhóm
    Grouped = Table.Group(Filtered, {"Thang"}, {{"Tong", each List.Sum([DoanhThu])}})
in
    Grouped

// Thêm cột tính toán
Table.AddColumn(Source, "LoiNhuan", each [DoanhThu] - [ChiPhi], type number)

// Đổi tên nhiều cột cùng lúc
Table.RenameColumns(Source, {{"OldName1", "TenMoi1"}, {"OldName2", "TenMoi2"}})

// Sắp xếp
Table.Sort(Source, {{"DoanhThu", Order.Descending}})

// Thêm cột index
Table.AddIndexColumn(Source, "STT", 1, 1, Int64.Type)

let
    // Import từ folder
    Source = Folder.Files("C:\Data\BaoCaoThang"),
    
    // Chỉ lấy file .xlsx
    FilteredXlsx = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx")),
    
    // Mở mỗi file, lấy sheet "BanHang"
    AddContent = Table.AddColumn(FilteredXlsx, "Data", each
        let wb = Excel.Workbook([Content])
        in wb{[Item="BanHang", Kind="Sheet"]}[Data]
    ),
    
    // Expand ra thành 1 bảng
    Expanded = Table.ExpandTableColumn(AddContent, "Data", {"MaDH","NgayBan","NhanVien","SanPham","SoLuong","ThanhTien"}),
    
    // Đổi kiểu dữ liệu
    Typed = Table.TransformColumnTypes(Expanded, {
        {"NgayBan", type date}, {"SoLuong", Int64.Type}, {"ThanhTien", type number}
    }),
    
    // Thêm cột Tháng từ tên file
    AddMonth = Table.AddColumn(Typed, "Thang", each Text.BetweenDelimiters([Name], "_T", "."), type text)
in
    AddMonth