Power Query Group By: Keep All Columns?

How-to basics · Updated November 22, 2026

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:

  1. Select the column(s) you want to group by.
  2. Click Group By on the Transform tab.
  3. In the dialog, click Advanced.
  4. Add a new aggregation and choose All Rows as the operation.
  5. Click OK. You’ll get a new column (e.g., “AllData”) that contains a table for each group.
  6. 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:

  1. Add an index column (Add Column > Index Column).
  2. 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.
  3. 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.Max to 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

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.