Move an Excel PivotTable Data Model to Power BI
Question details
The user needs to transfer Excel PivotTable or Power Query results to Power BI while preserving the source data and field structure.
- Product
- Microsoft Excel PivotTable, Power Query, and Power BI
- Device & OS
- not provided
- Scenario
- An Excel data model or query result is being loaded into Power BI, and the user wants both applications to produce consistent results.
- Observed behavior
- Different source fields, including fields in PQ_TableGrid, can prevent Power BI from returning the expected PivotTable results.
Open the Excel workbook and the Power BI file side by side. Confirm that both use the same source table and compare the Power Query output, especially the PQ_TableGrid fields, before loading the data.
Match the Excel source and field structure before loading data into Power BI
Use this approach when Power BI results differ from Excel or fields are missing after importing the data model.
Excel PivotTables and Power BI must use equivalent source data and field names to produce consistent results. Differences in Power Query tables or column names can change the available fields and expected output.
In Excel, open Data > Queries & Connections and inspect the query source and loaded table. In Power BI Desktop, select Home > Transform data and verify that the same source table is being used.
In Power Query Editor, select the PQ_TableGrid query or table and compare its column names, data types, and row structure between Excel and Power BI.
Rename or restore columns in Power Query so the Excel and Power BI versions use equivalent field names and compatible data types.
In the Excel PivotTable, right-click the Year field, select Field Settings > Layout & Print, choose Repeat item labels, and click OK.
Refresh the query in Excel, load the matching data into Power BI with Home > Close & Apply, and compare the PivotTable fields and results.
Prepare a flattened Excel PivotTable for Power BI import
Use this option when Power BI needs a tabular result with repeated category values instead of a visually grouped PivotTable layout.
Use WPS Office for compatible Excel PivotTable and workbook workflows
WPS Office is a free, lightweight Microsoft Office alternative for opening, editing, and organizing Excel workbooks before moving tabular data into Power BI. Its familiar interface and strong XLSX compatibility make migration from Excel straightforward, while Power BI-specific loading and data-model behavior remains managed in Microsoft Power BI.
- 1. Open the workbook: Install WPS Office, open the Excel workbook, and review the source tables and PivotTable structure in WPS Spreadsheets.
- 2. Prepare consistent fields: Use the spreadsheet tools to check column names, remove unwanted layout differences, and organize the source data for Power BI.
- 3. Save an Excel-compatible copy: Choose Menu > Save As, select the XLSX format, and save the prepared workbook before importing it into Power BI.
Frequently Asked Questions
Can an Excel PivotTable data model be directly moved to Power BI?
Power BI does not simply copy every Excel PivotTable feature or data-model relationship. Use the same source data and equivalent field names, then import the corresponding tables or Power Query results into Power BI.
Why do Excel and Power BI show different PivotTable fields?
The source tables or Power Query outputs may differ. Open Power Query Editor in both files and compare PQ_TableGrid, column names, data types, and transformations.
How can I repeat year values before importing an Excel result into Power BI?
In Excel, right-click the PivotTable year field, choose Field Settings > Layout & Print, select Repeat item labels, and click OK. Refresh the PivotTable before loading its data into Power BI.
Does Power BI preserve Excel PivotTable formatting and layout?
Power BI imports data and model elements rather than preserving every Excel PivotTable layout setting. Recreate the report visual and formatting in Power BI after confirming that the imported fields are consistent.
