Grouping, Aggregating, Pivoting, and Unpivoting

Quick Answer: Group By summarizes data (like SQL GROUP BY). Pivot turns row values into column headers (long → wide). Unpivot turns column headers into row values (wide → long). Transpose flips the entire table. Unpivot is the most commonly tested because it normalizes denormalized Excel data into a format suitable for data modeling.

Group By

Group By aggregates multiple rows into summary rows based on grouping columns.

Transform tab → Group By

Basic Group By

Group by one column with one aggregation:

Group By Region, Sum of Amount:

Advanced Group By

Group by multiple columns with multiple aggregations:

Group By → Advanced →
  Group By: Region, Product
  Aggregations:
    - Total Sales: Sum of Amount
    - Order Count: Count Rows
    - Average Order: Average of Amount

Available aggregation functions:

• Sum, Average, Median
• Min, Max
• Count Rows, Count Distinct Values
• All Rows (returns a table — useful for advanced scenarios)

All Rows Aggregation

The All Rows option creates a nested table for each group. This is powerful for:

• Getting the first or last row of each group
• Applying custom calculations within groups
• Accessing detail rows after aggregation

Pivot Column

Pivot transforms row values into column headers, converting data from long (normalized) to wide (denormalized) format.

Select the column to pivot → Transform tab → Pivot Column
→ Choose the values column → Select aggregation

Example: Pivoting

Before (Long Format):

After Pivoting Month column (Values: Sales):

Aggregation options when pivoting:

• Sum (default for duplicates)
• Average, Count, Min, Max
• Don't Aggregate (errors if duplicates exist)

Unpivot Columns

Unpivot is the reverse of Pivot — it converts column headers into row values, transforming from wide to long (normalized) format.

Select columns to unpivot → Transform tab → Unpivot Columns

Three Unpivot Options

Example: Unpivoting

Before (Wide Format — common in Excel reports):

After Unpivoting the month columns:

Best Practice: Use "Unpivot Other Columns" instead of "Unpivot Columns" when the source may add new columns over time (e.g., new months). This way, new columns are automatically unpivoted.

Why Unpivot Matters

Unpivoting is critical for data modeling because:

• Power BI models work best with normalized (long) data
• Star schema fact tables require one row per measurement
• DAX functions work most effectively with long-format data
• Filters and slicers apply naturally to normalized structures

Transpose

Transpose swaps the entire table's rows and columns:

Transform tab → Transpose

• Row 1 becomes Column 1, Row 2 becomes Column 2, etc.
• Column headers become the first row of data
• The first row of data becomes the new column headers

Common use case: Data sources where data is laid out horizontally (metrics as rows, time periods as columns) that need to be flipped to a standard vertical layout.

Tip: After transposing, use "Use First Row as Headers" to promote the first data row to column headers.

Semi-Structured Data Conversion

Converting semi-structured data (JSON, XML) into tables:

JSON to Table

// Parse JSON
Json.Document(Source)
// Convert record to table
Record.ToTable(jsonContent)
// Expand nested records/lists
Table.ExpandRecordColumn(previousStep, "Value", {"field1", "field2"})

XML to Table

// Parse XML
Xml.Document(Source)
// Navigate to the data nodes
// Expand nested tables

Expanding Records and Lists

Click the expand icon (two arrows) in the column header to expand records or lists.

On the Exam

The PL-300 frequently tests:

• Choosing between Pivot and Unpivot for a given data transformation
• Using Unpivot Other Columns for dynamic column handling
• Group By with multiple columns and aggregations
• Converting JSON semi-structured data into tabular format
• Understanding when to use Transpose vs. Unpivot