Can Power Query Create a PivotTable Layout in WPS Spreadsheets?
Question details
The user needs to know whether Power Query can reproduce a PivotTable report layout with multiple fields in the Columns area and how to create that layout after transforming data.
- Product
- WPS Spreadsheets - Power Query
- Device & OS
- not provided
- Scenario
- Reproducing a report that uses multiple fields in the Columns area of a PivotTable while using Power Query for data preparation.
- Observed behavior
- Power Query returns a structured table rather than an interactive PivotTable layout with expandable or collapsed fields.
Make sure the source data is loaded into Power Query and that every column has a clear header and appropriate data type before you transform or load it.
Load the Power Query result to the Data Model and create a PivotTable
Use this when you need an interactive PivotTable layout with expandable and collapsed fields after cleaning data in Power Query.
Power Query is designed for data import, cleaning, and shaping. The PivotTable is the report layer that provides the expandable and collapsed field structure, so they should be used together rather than as substitutes.
In the Power Query Editor, complete the cleanup, filtering, column selection, and any row-to-column transformation you need.
Click Home > Close & Load To in Power Query, choose Connection only, check Add this data to the Data Model, and confirm.
In WPS Spreadsheets, go to Insert > PivotTable, select Use an external data source or This workbook’s Data Model, and choose the query table.
Drag the required fields into Filters, Columns, Rows, and Values areas to reproduce the PivotTable layout.
Pivot a column inside Power Query when you need a static reshaped table
Use this if your goal is only to convert row values into column headers, not to build an interactive PivotTable report.
Build Interactive PivotTable Reports from Power Query Data in WPS Spreadsheets
WPS Spreadsheets lets you shape data in Power Query, load it into the Data Model, and then insert an interactive PivotTable with the exact Columns-area layout you need.
- 1. Download and install WPS Office: Get WPS Office from the official website, install it, and open WPS Spreadsheets.
- 2. Import your source data with Power Query: Go to Data > Get Data or From Table/Range, then clean and transform the data in Power Query Editor.
- 3. Load to the Data Model: Click Close & Load To and choose Connection only with Add this data to the Data Model.
- 4. Create the PivotTable: Go to Insert > PivotTable, select the Data Model as the source, and click OK.
- 5. Arrange the report fields: Drag fields to Columns, Rows, Filters, and Values to reproduce the required layout.
Frequently Asked Questions
Does Power Query replace a PivotTable?
No. Power Query shapes data and returns a structured table. A PivotTable is the interactive report layer; you can load the Power Query result to the Data Model and then insert a PivotTable.
How do I load Power Query data into the Data Model in WPS Spreadsheets?
In Power Query Editor, click Home > Close & Load To, choose Connection only, check Add this data to the Data Model, and confirm. Then use Insert > PivotTable and select the Data Model as the source.
Can I put multiple fields in the Columns area after using Power Query?
Yes. Once the transformed data is in the Data Model and you have inserted a PivotTable, drag the required fields into Filters, Columns, Rows, and Values to reproduce the report.
What should I do if I only need a reshaped table instead of a PivotTable?
Use Power Query's Transform > Pivot Column command to turn row values into column headers. This returns a static table, not an interactive PivotTable.
Are DAX formulas required to create this layout?
No, DAX is only needed when you want additional calculated measures in the Data Model. You can create the basic PivotTable layout with existing source fields.
