Why Power Query Columns Do Not Appear in an Excel Data Model PivotTable
Question details
The user needs to understand why formula columns added manually to a Power Query output table are not synchronized with the table loaded into the Excel Data Model, and how to make them available in a PivotTable.
- Product
- Microsoft Excel Power Query and Data Model
- Device & OS
- not provided
- Scenario
- The user created Power Query tables loaded to the Excel Data Model, built a PivotTable from the Data Model, then added extra columns to one output table and refreshed, but the new fields did not appear in the field list.
- Observed behavior
- Manually added formula columns are outside the query table definition, so they are not loaded into the Data Model and do not appear in the PivotTable field list after refresh.
Open the workbook and check whether the missing columns were added in the worksheet next to the Power Query output table instead of inside Power Query or in the Data Model. This determines why they are not included in the PivotTable field list.
Load a Second Power Query That Includes the Manually Added Columns
Use this when you already added columns next to the Power Query output table and need them to appear in an existing Data Model PivotTable.
Manually added columns are not part of the query table definition, so they are not included in the Data Model load. A second query can read the visible output table, including those extra columns, and load the complete result into the Data Model.
Click anywhere in the Power Query output table, go to the Data tab, and click From Table/Range to open the visible table in Power Query Editor.
In Power Query Editor, verify that the preview shows both the original query columns and the manually added columns, then click Close & Load To.
In the Import Data dialog, select Only Create Connection, check Add this data to the Data Model, and click OK.
Select the PivotTable, go to PivotTable Analyze > Refresh, and then open the second query table in the PivotTable field list to add the new columns.
Add Calculated Columns in Power Query Before Loading to the Data Model
Use this as the reliable long-term approach so every refresh carries the calculated columns into the Data Model automatically.
Add the Column Directly in the Excel Data Model
Use this when you prefer to keep calculations in the Data Model layer rather than in the worksheet or Power Query.
WPS Office: A Free Spreadsheet Alternative for Everyday PivotTable and Data Work
Because Power Query and Data Model behavior are Excel-specific, WPS does not change the internal Excel model. However, WPS Office is a free, lightweight Microsoft Office alternative that opens and saves Excel files, supports PivotTable workflows, and uses a familiar Ribbon-style interface for an easy switch.
- 1. Download WPS Office: Go to the official WPS Office download page, click Download WPS Office Free, and install the version for your device.
- 2. Open your Excel workbook: Launch WPS Spreadsheet, click Menu > Open, and select the .xlsx file that contains the data or PivotTable.
- 3. Create or refresh a PivotTable: Select the data range, go to the Insert tab, click PivotTable, and build the summary with WPS Spreadsheet tools.
Frequently Asked Questions
Why don’t columns I added next to a Power Query output table show up in the Data Model PivotTable?
Because Excel loaded only the query table into the Data Model. Adjacent cells added after the load are outside that query table, so Refresh All does not bring them into the model or the PivotTable field list.
How can I make manually added columns appear without redoing the entire query?
Create a second Power Query from the visible output table. In Excel, click the output table, go to Data > From Table/Range, and load the second query to the Data Model, then build or refresh the PivotTable from that connection.
What is the most reliable way to add calculated columns for a Data Model PivotTable?
Add them in Power Query with Add Column > Custom Column or in the Data Model/Power Pivot with a DAX formula. This keeps the column inside the query or model so it refreshes normally.
Does Refresh All fix missing Power Query columns?
Refresh All only refreshes existing connections. If the column is outside the loaded query table, it is not part of any connection, so it will not appear until you load a query that includes it.
Can WPS Office open an Excel file with a Data Model PivotTable?
WPS Office can open .xlsx workbooks and work with regular PivotTables. Complex Power Query and Data Model features are best managed in Excel, while WPS is suitable for compatible spreadsheet work.
