Group By & Power Query

🗂️

Group By

Tổng hợp dữ liệu theo nhóm — tương đương SQL GROUP BY

Before — Raw Orders

OrderID	Category	Amount
1001	Electronics	5,500,000
1002	Fashion	1,200,000
1003	Electronics	3,200,000
1004	Fashion	800,000
1005	Food	450,000

After — Group By Category

Category	Total Amount	Order Count
Electronics	8,700,000	2
Fashion	2,000,000	2
Food	450,000	1

Cách thực hiện Group By

Mở Power Query Editor → Home → Transform Data
Chọn cột muốn group (ví dụ: Category) → Home → Group By
Chọn Advanced để thêm nhiều aggregation cùng lúc
Thêm các aggregation: New column name = "Total Amount", Operation = Sum, Column = Amount
Thêm tiếp: "Order Count", Count Rows → OK

M Code (Advanced): Power Query tự generate M code. Bạn có thể edit thẳng trong Advanced Editor.

// M code tự generate từ Group By UI
= Table.Group(
    Source,
    {"Category"},              // Group by columns
    {
        {"Total Amount", each List.Sum([Amount]),    type number},
        {"Order Count",  each Table.RowCount(_), type number}
    }
)

Group By nhiều cột

// Group by Year + Category + Region
= Table.Group(
    Source,
    {"Year", "Category", "Region"},   // 3 group keys
    {
        {"Revenue",   each List.Sum([Amount]),     type number},
        {"Orders",    each Table.RowCount(_),    type number},
        {"Avg Order", each List.Average([Amount]), type number},
        {"Max Order", each List.Max([Amount]),     type number}
    }
)

↔️

Pivot & Unpivot

Chuyển đổi giữa wide format và long format

🔄 Unpivot (Wide → Long)

Chuyển nhiều cột thành 2 cột (Attribute + Value). Cần thiết khi data dạng bảng chéo.

Chọn các cột muốn unpivot
Transform → Unpivot Columns
Rename "Attribute" → "Month", "Value" → "Revenue"

🔀 Pivot (Long → Wide)

Chuyển giá trị của 1 cột thành nhiều cột header. Ngược với Unpivot.

Chọn cột chứa header values (Month)
Transform → Pivot Column
Values column = Revenue, Aggregate = Sum

Wide Format (Pivot) — khó dùng trong Power BI

Product	Jan	Feb	Mar
A	100	120	95
B	200	180	220

Long Format (Unpivot) — chuẩn cho Power BI

Product	Month	Revenue
A	Jan	100
A	Feb	120
A	Mar	95
B	Jan	200

Rule: Power BI hoạt động tốt nhất với Long Format (tidy data). Nếu data đến từ Excel ở dạng Wide, luôn Unpivot trước trong Power Query.

🔗

Merge Queries (JOIN)

Kết hợp 2 bảng — tương đương SQL JOIN

Join Types

Left Outer — giữ toàn bộ bảng trái (giống LEFT JOIN)
Right Outer — giữ toàn bộ bảng phải
Full Outer — giữ tất cả (FULL JOIN)
Inner — chỉ giữ match ở cả 2 (INNER JOIN)
Left Anti — hàng trong trái KHÔNG có trong phải
Right Anti — hàng trong phải KHÔNG có trong trái

Cách thực hiện

Home → Merge Queries
Chọn bảng 2 cần join
Click chọn cột join key ở bảng 1
Click chọn cột join key ở bảng 2
Chọn Join Kind → OK
Expand cột mới → chọn field cần lấy

// M code: Left join Orders + Products
= Table.NestedJoin(
    Orders,           // bảng trái
    {"ProductID"},   // join key trái
    Products,         // bảng phải
    {"ProductID"},   // join key phải
    "Products",      // tên cột nested table
    JoinKind.LeftOuter
)

Khác với Relationship: Merge Queries tạo ra bảng mới đã join sẵn (denormalized). Relationship trong Data Model giữ nguyên 2 bảng riêng và join on-the-fly khi query. Dùng Relationship khi có thể — hiệu quả hơn.

📎

Append Queries (UNION)

Ghép nhiều bảng cùng cấu trúc thành 1

Có nhiều file Excel cùng cấu trúc: Sales_2022.xlsx, Sales_2023.xlsx, Sales_2024.xlsx
Tạo query cho từng file (hoặc dùng Folder Connector để load toàn bộ)
Home → Append Queries → chọn "Three or more tables"
Add tất cả các query cần ghép → OK

// Append 3 bảng
= Table.Combine({Sales_2022, Sales_2023, Sales_2024})

Folder Connector: Thay vì append thủ công, dùng Get Data → Folder → trỏ vào folder chứa tất cả Excel → Power Query tự load và combine tất cả file trong folder. Thêm file mới = tự động include khi Refresh.

➕

Custom Column & Conditional Column

Tạo cột mới với logic trong Power Query

Custom Column (M expression)

// Full Name từ First + Last
[FirstName] & " " & [LastName]

// Profit Margin %
[Profit] / [Revenue] * 100

// Date extraction
Date.Year([OrderDate])
Date.MonthName([OrderDate])

// Text cleaning
Text.Trim(Text.Upper([ProductName]))

// Conditional
if [Amount] > 1000000
then "High Value"
else if [Amount] > 500000
then "Medium"
else "Low"

Conditional Column (UI builder)

Add Column → Conditional Column → định nghĩa các điều kiện qua UI, không cần viết M code. Phù hợp cho logic đơn giản.

Column From Examples

Add Column → Column From Examples → gõ ví dụ kết quả bạn muốn, Power Query tự đoán pattern và generate M code. Cực kỳ hữu ích cho text transformation.

🧹

Data Cleaning Checklist

Các bước làm sạch data bắt buộc trước khi load

✅ Data Type

Date/Time columns → Date type (không phải Text)
Revenue, Qty → Decimal Number hoặc Whole Number
ID columns → Text (dù là số — tránh bị aggregate)
% columns → Decimal Number (0.15 không phải 15)

🗑️ Null & Duplicate

Home → Remove Rows → Remove Blank Rows
Home → Remove Rows → Remove Duplicates (chọn key columns)
Replace Value: null → 0 (hoặc "N/A")
Filter: loại bỏ test data ("DELETE", "TEST123")

✂️ Text Cleaning

Transform → Trim (xóa space đầu/cuối)
Transform → Clean (xóa ký tự không in được)
Transform → Capitalize Each Word / UPPERCASE / lowercase
Replace "VN" → "Vietnam", "HCM" → "Ho Chi Minh" cho nhất quán

📅 Date Handling

Add Column → Date → Year/Month/Day/Quarter
Add Month Name: Date.MonthName([Date])
Add Week Number: Date.WeekOfYear([Date])
Fiscal Year: if Date.Month([Date]) >= 4 then Date.Year([Date]) else Date.Year([Date]) - 1

Group By, Aggregate & Transform