Field Aggregation & Data Manipulation

When working in Power BI, two very important concepts are:

1. Field Aggregation → How numbers are summarized
2. Data Manipulation → How data is transformed or modified

These are fundamental for data analysis.

1️⃣ FIELD AGGREGATION

🔹 What is Field Aggregation?

Aggregation means combining multiple values into one summary value.

For example:

| OrderID | Customer | Amount |
|---------|----------|--------|
| 1001    | John     | 200    |
| 1002    | John     | 300    |
| 1003    | Jane     | 150    |

If we aggregate by Customer and SUM the Amount:

| Customer | Total Sales |
|----------|------------|
| John     | 500        |
| Jane     | 150        |

We combined multiple rows into one summarized result.

🔹 Common Aggregation Types

AggregationMeaningExample ResultSumAdd values200 + 300 = 500AverageMean value(200 + 300) / 2CountNumber of rows2 ordersMinSmallest value150MaxLargest value300Distinct CountUnique valuesUnique customers

🔹 Aggregation in Power BI Visuals

When you drag a numeric field (like Amount) into a visual:

Power BI automatically aggregates it (usually Sum).

You can change it:

• Click dropdown on field
• Choose Sum / Average / Count / Min / Max

🔹 Aggregation Using Power Query (Group By)

In Power Query Editor:

1. Select column (e.g., Customer)
2. Click Home → Group By

Example M Code:

= Table.Group(Source,
    {"Customer"},
    {{"Total Sales", each List.Sum([Amount]), type number}}
)

Explanation:

• Group by Customer
• Sum the Amount column

🔹 Multiple Aggregations Example

= Table.Group(Source,
    {"Customer"},
    {
        {"Total Sales", each List.Sum([Amount]), type number},
        {"Average Sales", each List.Average([Amount]), type number},
        {"Order Count", each List.Count([OrderID]), Int64.Type}
    }
)

2️⃣ DATA MANIPULATION

🔹 What is Data Manipulation?

Data manipulation means:

• Changing values
• Creating new columns
• Splitting columns
• Merging tables
• Filtering data
• Transforming data types

It prepares data for analysis.

1️⃣ Creating a New Column (Calculated Column in Power Query)

Example: Add Tax (10%)

| Amount |
|--------|
| 200    |
| 300    |

We create:

| Amount | Tax |
|--------|-----|
| 200    | 20  |
| 300    | 30  |

M Code:

= Table.AddColumn(Source, "Tax", each [Amount] * 0.10, type number)

2️⃣ Conditional Column (IF Statement)

Example:

If Amount > 500 → "High"

Else → "Normal"

= Table.AddColumn(Source, "Sales Category",
    each if [Amount] > 500 then "High" else "Normal"
)

Result:

| Amount | Sales Category |
|--------|---------------|
| 200    | Normal        |
| 700    | High          |

3️⃣ Filtering Rows

Remove small sales:

= Table.SelectRows(Source, each [Amount] > 100)

4️⃣ Replacing Values

Replace "USA" with "United States":

= Table.ReplaceValue(Source,"USA","United States",
    Replacer.ReplaceText,{"Country"}
)

5️⃣ Splitting Columns

If you have:

| Full Name |
|-----------|
| John Doe  |

Split into:

| First Name | Last Name |
|------------|----------|
| John       | Doe      |

M Code:

= Table.SplitColumn(Source,
    "Full Name",
    Splitter.SplitTextByDelimiter(" "),
    {"First Name","Last Name"}
)

6️⃣ Merging Tables (Join)

Suppose:

Sales Table:

| CustomerID | Amount |
|------------|--------|
| 1          | 200    |

Customer Table:

| CustomerID | Customer Name |
|------------|--------------|
| 1          | John         |

Merge:

= Table.NestedJoin(Sales, {"CustomerID"},
                   Customers, {"CustomerID"},
                   "CustomerTable", JoinKind.LeftOuter)

7️⃣ Changing Data Types

Very important!

= Table.TransformColumnTypes(Source,{
    {"Amount", type number},
    {"Order Date", type date}
})

Wrong data types cause incorrect aggregations.

PRACTICAL FULL EXAMPLE

Raw Data:

| OrderID | Customer | Amount |
|---------|----------|--------|
| 1001    | John     | 200    |
| 1002    | John     | 300    |
| 1003    | Jane     | 800    |
| 1004    | Jane     | 50     |

Full Transformation Script

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],

    // Change data types
    ChangedType = Table.TransformColumnTypes(Source,{
        {"OrderID", Int64.Type},
        {"Customer", type text},
        {"Amount", type number}
    }),

    // Add Sales Category
    AddCategory = Table.AddColumn(ChangedType,
        "Sales Category",
        each if [Amount] > 500 then "High" else "Normal"
    ),

    // Add Tax column
    AddTax = Table.AddColumn(AddCategory,
        "Tax",
        each [Amount] * 0.10,
        type number
    ),

    // Group by Customer
    GroupedData = Table.Group(AddTax,
        {"Customer"},
        {
            {"Total Sales", each List.Sum([Amount]), type number},
            {"Average Sales", each List.Average([Amount]), type number},
            {"Order Count", each List.Count([OrderID]), Int64.Type}
        }
    )

in
    GroupedData