Group Excel Power Query Data Without Breaking the Connection
Question details
Group repeated team and sport values into a reorganized layout without changing the connected query source.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- An Excel query retrieves data from SharePoint or another source, and the user wants a grouped presentation of its results.
- Observed behavior
- The desired layout displays team, sport, and division values in a grouped or reordered view while retaining the query connection.
Confirm that the query results are loaded to an Excel table and identify the team, sport, and division columns. If you use the formula method, check that your Excel version supports LET, VSTACK, DROP, and CHOOSECOLS.
Group the query results with a PivotTable
Use a PivotTable for a grouped presentation without editing the query or its source.
Click any cell in the table containing the loaded Power Query results.
Choose Insert > PivotTable, confirm the query table as the source, and select where to place the PivotTable.
In PivotTable Fields, drag Team, Sport, and Division into the Rows area in the order you want them grouped.
Select the PivotTable and choose Design > Report Layout > Show in Tabular Form. Then choose Design > Report Layout > Do Not Repeat Item Labels to suppress repeated row labels.
After the query data changes, use Data > Refresh All to refresh the query and PivotTable.

Reorder columns and suppress repeated values with a formula
Use a Microsoft 365 dynamic-array formula when you want to retain one output row per query result.
Organize spreadsheet data with WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel files, making it easy to move everyday spreadsheet work. It does not repair or guarantee preservation of Excel Power Query connections.

Frequently Asked Questions
Will a PivotTable change my Power Query source?
No. A PivotTable based on the loaded query table presents the results separately; it does not edit the query steps or source data.
Why does my PivotTable show fewer rows than the query table?
A PivotTable groups identical field combinations. To distinguish individual records, add a unique identifier to the Rows area, or use the formula method to retain one output row per source row.
How do I update the grouped view after the source changes?
Choose Data > Refresh All in Excel to refresh the query and PivotTable. A dynamic-array formula linked to the query table recalculates from the updated table.
Why does the formula show a #SPILL! error?
The output area may contain data or the formula may be inside an Excel table. Move the formula outside the table and clear the cells needed for its spilled results.
Can WPS Office preserve my Excel Power Query connection?
WPS Office can open Excel files, but preservation or refresh of Power Query connections is not guaranteed. Use Excel to maintain and refresh the connected query.




