'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Power Query Showing More Rows Than Excel Source Table
Question details
Power Query is returning significantly more rows than the original Excel source table, indicating unintended data duplication.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Transforming, merging, or loading data from an Excel table using Power Query Editor.
- Observed behavior
- The final query result contains a highly inflated row count (e.g., 10,000 rows) compared to the original source data (e.g., 5,000 rows).
Before troubleshooting, open the Power Query Editor and ensure the status bar is visible at the bottom of the window so you can easily track exact row numbers as you click through your query steps.
Review Applied Steps to Locate Duplications
Identify the exact operation causing row duplication by carefully reviewing the Applied Steps pane in the Power Query Editor.
In most cases, row inflation occurs because of a misconfigured merge, an append operation, or expanding nested tables that contain multiple matching records. Tracking the row count step-by-step is the most reliable way to find the root cause.
Navigate to the Data tab in the Excel ribbon, click on 'Get Data', and select 'Launch Power Query Editor' to view your active queries.
In the 'Applied Steps' pane on the right side of the editor, click through each step individually from top to bottom.
Watch the row count indicator at the bottom left of the editor. Note the exact step where the row count incorrectly jumps from the expected number (e.g., 5,000) to the inflated number (e.g., 10,000).
Click the gear icon next to the identified step to edit its settings. Adjust the merge criteria, filter out duplicate matching keys, or correct the expanded table settings to ensure the row count returns to the expected 5,000 rows.
Remove Duplicate Rows from the Output
If finding the specific step is difficult, you can force Power Query to remove duplicate rows before loading the final data.
Handle Large Datasets Smoothly with WPS Office
If complex Power Query setups in Microsoft Excel are causing data duplication and frustration, consider WPS Office. WPS Spreadsheet provides highly intuitive data processing, robust duplicate removal tools, and seamless format compatibility without the steep learning curve.
- 1. Download WPS Office: Visit the official WPS Office website and download the free installation package.
- 2. Open Your Dataset: Launch WPS Spreadsheet and easily open your existing Excel workbooks without losing formatting.
- 3. Clean Your Data: Navigate to the Data tab to access simple, powerful tools to highlight and remove duplicate rows instantly.
Frequently Asked Questions
Why does expanding a merged table in Power Query multiply my rows?
When you merge tables and expand the nested data, Power Query creates a Cartesian product for multiple matching records. If the second table has three rows for a single matching key in the first table, your row count for that record multiplies by three.
How do I easily track row counts in the Power Query Editor?
You can view the current row count in the status bar at the bottom left of the Power Query Editor window. It updates automatically as you click through different steps in the Applied Steps pane.
Does removing duplicates in Power Query delete data in my original Excel file?
No, Power Query operations only affect the queried data being loaded into the data model or a new worksheet. Your original source table in Excel remains completely untouched and unchanged.
