? Group By & Transpose Data
✨ Quick Overview
Group By collapses multiple rows into summaries based on a common value. Transpose flips the table structure (rows become columns). Both are essential for fixing "messy" data layouts.
? key Concepts
Group By: Reduces the row count by aggregating data (Sum, Average, Count).Transpose: Swaps the axis of the table. Usually followed by "Use First Row as Headers."
? Navigation Path
Location: Power Query Editor → Transform Tab → Group By / Transpose.
? Sample Dataset
? View Raw Data
City,Category,Amount Pune,Electronics,15000 Pune,Electronics,12000 Mumbai,Electronics,45000
? Basic vs. Advanced Group By
Basic: Aggregate by one column (e.g., Total Sales by City).Advanced: Aggregate by multiple columns (e.g., Total Sales by City AND Category).
? Use Cases
Group By: Converting thousands of daily transactions into a monthly summary to speed up the report.Transpose: Fixing Excel sheets where dates are listed horizontally across columns instead of vertically in rows.
⚡ Reshaping Simulator
Experiment with how Power Query reshapes the data:
Group By City (Sum) Transpose Table Reset
Original Table
City
Amount
Pune
100
Pune
200
Mumbai
500
✅ Tips & Best Practices
Group By Early: If your raw data has 1 million rows but you only need city-level data, Group By in Power Query to make your .pbix file smaller.Rename Totals: Always rename the default "Count" column to something descriptive like "Total Revenue."Transpose Warning: Transposing a very large table (thousands of rows) can be extremely slow; use it only for structural fixes.Data Types: Ensure your values are "Decimal Number" or "Whole Number" before trying to Group By; you cannot sum Text!
? Try It Yourself
Import the Sales dataset.
Select the City column → Group By → Operation: Sum of Amount .
Undo that step, then try Advanced Group By using City and Category.
On a fresh table, click Transpose and observe how the headers move to the first row of data.