To ungroup data in Excel Power Query, select the grouped column, then go to the Transform tab and click Group By again, but this time choose All Rows as the aggregation and then expand the resulting column, or simply use the Ungroup button in the Query Editor’s Transform tab (if available) to remove grouping from a column that was previously grouped. The most reliable method is to remove the grouping step from the Applied Steps pane on the right side of the Power Query Editor.
What Does “Group” Mean in Power Query?
In Power Query, grouping is a transformation that combines rows that share the same value in one or more columns. When you group, you aggregate the remaining columns using functions like Sum, Average, Count, or All Rows. The result is a table with one row per group, and the aggregated data is either a single value (e.g., total sales) or a nested table (if you choose All Rows). Grouping is often used for summarization, but sometimes you need to reverse it—especially if you grouped by accident or if you received a file that was already grouped and you want to restore the original detail rows.
How to Ungroup in Power Query: Step-by-Step
The simplest and most common way to ungroup is to delete the grouping step from the query. Here’s how:
- Open the Power Query Editor (in Excel: Data > Get Data > Launch Power Query Editor).
- In the Query Settings pane on the right, find the Applied Steps list.
- Locate the step named Grouped Rows (or similar, like “Grouped Rows1”).
- Click the X on the right of that step to delete it. This removes the grouping and restores the data to its previous state.
- If the grouping is the first step after the source, deleting it will return you to the raw data.
If you don’t want to delete the step (because you need it for other purposes), you can instead create a new query that references the original source and does not apply the grouping. But for most cases, deleting the step is the fastest fix.
Ungrouping When Data Is Already Grouped (Expanding Nested Tables)
Sometimes you receive data that has been grouped using All Rows, which creates a column containing nested tables. To ungroup—meaning to expand those nested tables back into individual rows—you use the Expand button:
- In the Power Query Editor, click the expand icon (a double arrow) in the header of the column that contains the Table values.
- Choose the columns you want to bring back, or select Select All Columns.
- Uncheck Use original column name as prefix if you don’t want prefixes.
- Click OK. The nested tables are expanded back into rows.
This is effectively an “ungroup” because it reverses the grouping that created the nested tables. Note that if the grouping used an aggregation like Sum, you cannot recover the original rows—the detail is lost. Only All Rows preserves the underlying data.
What If There Is No “Ungroup” Button?
Many users look for a dedicated Ungroup button in the ribbon. In the Power Query Editor, there is no single button labeled “Ungroup” in the standard interface. The closest is the Group By button itself, which opens a dialog where you can change the aggregation. If you want to remove grouping, you must delete the step or expand nested tables. In Excel’s main ribbon, there is an Ungroup button (under Data > Outline), but that is for worksheet outline grouping (row/column groups), not Power Query. So if you’re in the wrong place, that might be the source of confusion.
Common Problems and Misconceptions
One common misconception is that grouping and ungrouping are symmetric operations. They are not. If you group by a column and sum a numeric column, the original individual rows are gone. You cannot “ungroup” to get them back unless you had previously used All Rows or you have a backup of the original data. Another issue is that deleting the grouping step might break later steps that depend on the grouped structure. For example, if you added a custom column that references the grouped column, deleting the step will cause an error. In that case, you need to reorder steps or adjust the dependent steps.
Another frequent problem is that the Group By dialog in Power Query offers two modes: Basic and Advanced. In Basic mode, you can only group by one column and use one aggregation. In Advanced mode, you can group by multiple columns and add multiple aggregations. If you accidentally grouped in Advanced mode with several aggregations, ungrouping still means deleting the step or expanding if you used All Rows for one of the aggregations.
Alternatives to Ungrouping
If your goal is to analyze data that is currently grouped, you might not need to ungroup at all. Instead, consider:
- Using the grouped data as-is for summary reports.
- Creating a new query from the original source and applying a different set of transformations.
- Duplicating the query and removing the grouping step in the duplicate, keeping the original for other uses.
- Using
Table.ExpandTableColumnin the Advanced Editor if you need to programmatically expand nested tables.
For example, if you have a query that groups sales by region and sums revenue, but you also need the individual transactions, you can duplicate the query, delete the grouping step, and keep both queries in the workbook.
Frequently Asked Questions
Can I ungroup in Power Query without losing data? Yes, if you used All Rows as the aggregation, you can expand the nested tables back to individual rows. If you used Sum or Count, the original rows are lost and cannot be recovered from the grouped query alone.
Why is the Group By button greyed out? The Group By button is disabled when the current step’s result is not a table (e.g., if you have a single value or a list). Make sure you are on a step that outputs a table, and that the column you want to group by is selected.
Does ungrouping affect the original Excel data? No. Power Query transformations are applied to a copy of the data loaded into the query editor. The original source data remains unchanged. Your changes only affect the query output when you load it back to a worksheet.
Is there a keyboard shortcut for ungrouping? There is no dedicated shortcut for ungrouping in Power Query. You can press Ctrl+Z to undo the last action, which might revert a grouping if you just applied it. On a Mac, use Cmd+Z.
What if I want to ungroup multiple columns at once? If you grouped by multiple columns, deleting the grouping step will remove all groupings at once. If you have nested tables from an All Rows grouping, you can expand them all at once by selecting the expand icon on each nested table column, or by using the Expand button on the column header.
Can I ungroup in Excel for the web? Power Query is available in Excel for the web, but the interface is slightly simplified. The Applied Steps pane is accessible, and you can delete steps or expand nested tables. The steps are the same as in the desktop version, but some advanced options may be hidden.
Related
- Excel Hourly Schedule Template: How to Make One
- Excel: How to Add Lines in a Cell (3 Easy Ways)
- Excel Holiday Schedule: How to Build One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Can Google Sheets Open Excel Files?Yes. Google Sheets can open and edit many Microsoft Excel files, including .xlsx and .xls workbooks, either directly…
- Flowchart in PowerPoint: Create, Connect, and FormatYes. In Microsoft PowerPoint for Microsoft 365, make a flowchart by choosing Insert > Shapes, selecting symbols from…
- Excel Conditional Formatting for Past-Due DatesTo highlight past-due dates in Excel, select the cells or rows you want to format, choose Home >…
- Excel Copy and Paste Rows Into Columns: 3 Easy MethodsTo copy a row into a column in Excel for Microsoft 365 on Windows, select the row cells,…
- Due Dates in Google Sheets: Set Up Reminders and TrackingTo track due dates in Google Sheets, enter each deadline in a date-formatted column, then use Format >…
- Analysis ToolPak in Excel: What It Does and How to Enable ItIf you mean Excel’s Analysis ToolPak, it is a Microsoft Excel add-in that provides statistical and engineering analysis…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.