Power Query Problems

Group Excel Power Query Data Without Breaking the Connection

Huda QurayshiHuda Qurayshi Nov 18, 2026 869 views

Question details

Group repeated team and sport values into a reorganized layout without changing the connected query source.

Group Excel Power Query Data Without Breaking the Connection
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.
Before you start

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.

Solution 1Recommended

Group the query results with a PivotTable

Use a PivotTable for a grouped presentation without editing the query or its source.

1
Select the query table

Click any cell in the table containing the loaded Power Query results.

2
Insert a PivotTable

Choose Insert > PivotTable, confirm the query table as the source, and select where to place the PivotTable.

3
Arrange the row fields

In PivotTable Fields, drag Team, Sport, and Division into the Rows area in the order you want them grouped.

4
Adjust the layout

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.

5
Refresh the view

After the query data changes, use Data > Refresh All to refresh the query and PivotTable.

Group the query results with a PivotTable
Grouping changes the presentation: A PivotTable groups identical combinations rather than preserving every source row. Add a unique row identifier or use the formula method if each record must remain visible.
Free Microsoft Office alternative

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.

Organize and review team and sport data in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar interface for everyday spreadsheet tasksMove routine spreadsheet work to a free, lightweight office suite
QA img-9

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.