No, Power Query’s Group By does not keep all columns by default; it only keeps the grouping column(s) and the aggregated columns you explicitly add. However, you can retain all original columns by using the “All Rows” aggregation and then expanding the resulting tables, or by adding an index column before grouping and merging back. This article explains the default behavior, the workarounds, and when each approach is best.
Why Group By Drops Columns by Default
When you use the Group By feature in Power Query (available in Excel and Power BI), the operation is designed to summarize data. The default output includes only the columns you choose as grouping keys and the columns you create with aggregations (like Sum, Average, Count Rows, etc.). Any other columns from the original table are not carried over because they would be ambiguous—each group may contain multiple different values in those columns, so Power Query cannot decide which one to show.
The “All Rows” Aggregation: Keep Everything
The most straightforward way to keep all columns is to use the “All Rows” aggregation option. Here’s how:
- Select the column(s) you want to group by.
- Click Group By on the Transform tab.
- In the dialog, click Advanced.
- Add a new aggregation and choose All Rows as the operation.
- Click OK. You’ll get a new column (e.g., “AllData”) that contains a table for each group.
- Click the expand icon (double arrow) in that column header, and choose the columns you want to include. You can also uncheck “Use original column name as prefix” if you don’t want prefixes.
This method retains every column from the original table, but note that it does not reduce rows—it just groups the data into nested tables. If you want a single row per group with all columns, you need to combine this with other steps, such as extracting the first or last row from each table.
Using an Index Column and Merge: A More Controlled Approach
If you want to keep all columns but also have a single row per group (e.g., the row with the maximum value in a certain column), you can use an index column and a self-merge:
- Add an index column (Add Column > Index Column).
- Group by your desired column(s) and use the Max aggregation on the index column to find the row with the highest index per group.
- Merge the original table with the grouped table (on the group column and the index column) to bring back all columns for those specific rows.
This approach is more precise because you control which row from each group survives. It’s especially useful when you need the latest record per group (e.g., most recent transaction) while retaining all its attributes.
How to Choose the Right Method
Consider the following factors when deciding between “All Rows” and index/merge:
- Need for a single row per group? If yes, use index/merge or extract a row from the “All Rows” tables.
- Need to perform further aggregations? “All Rows” keeps all data, so you can later expand and re-aggregate flexibly.
- Performance: “All Rows” can be memory-intensive for large datasets because it stores nested tables. Index/merge may be faster if you only need one row per group.
- Simplicity: For quick exploration, “All Rows” is easier to set up.
Common Problems and Solutions
Here are typical issues users encounter when trying to keep all columns:
- Expansion changes data types: After expanding, check that columns have the correct data types. Sometimes Power Query infers types incorrectly; adjust them as needed.
- Duplicate column names: When expanding, if you have columns with the same name in different tables, use the prefix option to avoid conflicts.
- Losing columns in the merge: When merging, ensure you select the correct join keys and expand only the columns you need, avoiding duplication.
- Performance issues: If the dataset is large, consider using the index/merge approach instead of “All Rows” to reduce memory usage.
Alternatives to Group By for Keeping All Columns
Sometimes Group By isn’t the best tool. Depending on your goal, consider these alternatives:
- Pivot/Unpivot: If you need to transform data structure, use Pivot Column or Unpivot Columns instead of grouping.
- Add a custom column: Use a custom column with a formula like
Table.Maxto get the row you want without grouping. - Use the “Group By” with “Count Rows” and then merge: This gives you a summary count while keeping all rows via a merge, but it’s less efficient than index/merge.
Frequently Asked Questions
Can I keep all columns without using “All Rows”?
Yes, you can use the index/merge method or a custom column with a table function. The key is to identify which row from each group you want to keep, then bring back all columns for that row.
Does “All Rows” keep the original column order?
When you expand the “All Rows” column, the columns appear in the order they were in the original table, but you can reorder them afterward. The expansion dialog lets you select which columns to include.
Why does Group By not show all columns in the output?
Because each group can contain multiple values in non-grouping columns, Power Query cannot determine a single value to display. Aggregations like Sum or Average are deterministic, but raw columns are not.
Can I keep all columns and still get a single row per group?
Yes, use the “All Rows” aggregation and then extract the first row from each table (e.g., by adding a custom column with Table.First). Or use the index/merge method for more control.
Is there a way to keep all columns without expanding tables?
Not directly—Group By always returns a summarized table. To keep all columns, you must either use “All Rows” and expand, or use a merge operation to bring back the original columns.
Final Thoughts
While Power Query’s Group By doesn’t keep all columns by default, the “All Rows” aggregation and the index/merge technique give you full control. Choose the method based on whether you need a single row per group or want to retain all rows for further processing. Always test on a sample of your data to ensure the output matches your expectations.
Related
- How to Calculate Subtotal in Google Sheets
- Counter in Python: How to Use collections.Counter
- Traffic Light Changer Device: How It Works & Legal Risks
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Creating an Audit Tool in Excel: A Practical GuideTo create an audit tool in Excel, build a checklist table with one row per test, use dropdowns…
- How to Set Out of Office Dates in Outlook for Any DeviceTo set out-of-office dates in Outlook, open Automatic Replies, turn on replies for a specific time period, enter…
- Customer List Template Excel: Set Up a Useful SheetA customer list template in Excel is a structured worksheet for storing contact details, relationship notes, and follow-up…
- Excel Checklist Tricks for Faster, Clearer TrackingFor a quick checklist in Excel for Microsoft 365 on Windows, put each task in a cell, select…
- Currency Rate in Google Sheets: Formulas and TipsTo get a currency rate in Google Sheets, use the GOOGLEFINANCE function with a currency pair, such as…
- Excel Table with Subcategories: Setup and ExamplesYes. To make an Excel table with subcategories, put the main category and its subcategory in separate columns,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.