ExamFinch.com My Study

Study guide

3.2 Grouping, Aggregating, Pivoting, and Unpivoting

Practice this exam

Microsoft Power BI Data Analyst PL-300

Track your progress as you read

Finished sections get a check mark.

1 Introduction

About the PL-300 Exam The Power BI Ecosystem

2 Chapter 1: Get or Connect to Data

1.1 Data Source Types and Connections 1.2 Storage Modes: Import, DirectQuery, and Direct Lake 1.3 Data Source Settings and Parameters

3 Chapter 2: Profile and Clean the Data

2.1 Data Profiling in Power Query 2.2 Resolving Errors, Nulls, and Data Quality Issues 2.3 Data Types and Type Detection

4 Chapter 3: Transform and Load the Data

3.1 Column Transformations and Data Types 3.2 Grouping, Aggregating, Pivoting, and Unpivoting 3.3 Merge Queries, Append Queries, and Relationship Keys 3.4 Query Folding and Load Performance

5 Chapter 4: Design and Implement a Data Model

4.1 Star Schema Design 4.2 Relationships, Cardinality, and Cross-Filter Direction 4.3 Table and Column Properties 4.4 Calculated Columns and Calculated Tables

6 Chapter 5: Create Model Calculations with DAX

5.1 DAX Fundamentals and Measures 5.2 The CALCULATE Function 5.3 Time Intelligence Measures 5.4 Statistical Functions, Semi-Additive Measures, and Calculation Groups 5.5 Essential DAX Patterns and Functions Reference

7 Chapter 6: Optimize Model Performance

6.1 Performance Analyzer and DAX Query View 6.2 Model Size Reduction and Cardinality Management 6.3 DAX Optimization Patterns

8 Chapter 7: Create Reports

7.1 Visual Types and Selection 7.2 Visual Formatting and Conditional Formatting 7.3 Copilot in Power BI Reports 7.4 Report Page Configuration and Slicers

9 Chapter 8: Enhance Reports for Usability and Storytelling

8.1 Bookmarks, Navigation, and Drillthrough 8.2 Sorting, Selection Pane, Mobile Layout, and Accessibility 8.3 Export Settings and Automatic Page Refresh

10 Chapter 9: Identify Patterns and Trends

9.1 The Analyze Feature and AI Visuals 9.2 Using Copilot to Summarize the Semantic Model 9.3 Advanced Analytics Patterns

11 Chapter 10: Create and Manage Workspaces and Assets

10.1 Workspaces, Apps, and Content Distribution 10.2 Subscriptions, Alerts, and Semantic Model Refresh 10.3 Dashboards and Advanced Distribution

12 Chapter 11: Secure and Govern Power BI Items

11.1 Row-Level Security (RLS) 11.2 Sensitivity Labels, Item Access, and Governance 11.3 Deployment Pipelines and Content Lifecycle

3.2 Grouping, Aggregating, Pivoting, and Unpivoting

Key Takeaways Explain This Topic AI

Group By aggregates rows based on one or more columns, applying functions like Sum, Count, Average, Min, Max.

Pivot Column transforms unique row values into column headers — turning long format into wide format.

Unpivot Columns converts column headers back into row values — turning wide format into long format.

Transpose swaps rows and columns entirely, useful for data that is laid out horizontally.

Unpivot is particularly important for normalizing Excel reports with months or categories as column headers.

Last updated: June 2026

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:

  • Before: Sales Transactions
  • Region Amount
  • East 100
  • East 200
  • West 150
  • West 250

Group By Region, Sum of Amount:

  • After: Grouped
  • Region Total Amount
  • East 300
  • West 400
  • 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):

  • Product Month Sales
  • Widget Jan 100
  • Widget Feb 150
  • Gadget Jan 200
  • Gadget Feb 250

After Pivoting Month column (Values: Sales):

  • Product Jan Feb
  • Widget 100 150
  • Gadget 200 250

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

  • Option Behavior
  • Unpivot Columns Unpivots the selected columns
  • Unpivot Other Columns Keeps selected columns and unpivots everything else
  • Unpivot Only Selected Columns Same as Unpivot Columns but generates different M code
  • Example: Unpivoting

Before (Wide Format — common in Excel reports):

  • Product Jan Sales Feb Sales Mar Sales
  • Widget 100 150 200
  • Gadget 200 250 300

After Unpivoting the month columns:

  • Product Attribute Value
  • Widget Jan Sales 100
  • Widget Feb Sales 150
  • Widget Mar Sales 200
  • Gadget Jan Sales 200
  • Gadget Feb Sales 250
  • Gadget Mar Sales 300

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
  • Column Display Type Action
  • Record Single nested object Expand columns (select fields)
  • List Array of values Expand to new rows
  • Table Nested table Expand columns or rows

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