Fix Missing Rows in an Excel Power Query Text File Import
Question details
The user needs to import all records from an updated tab-delimited text file into Excel Power Query.

- Product
- Microsoft Excel 2016 Power Query
- Device & OS
- not provided
- Scenario
- A Power Query imports a tab-delimited text file after its source location changed.
- Observed behavior
- The query shows 14,568 rows from a file expected to contain 15,500 lines, with the discrepancy appearing at the Source step.
Confirm the full path and name of the updated text file and check its expected line count. In Excel, identify the query that imports the file.
Update the Power Query source path and refresh
Use this solution when the query may still point to an older copy of the text file.
In Excel, go to Data > Show Queries, then double-click the affected query to open Power Query Editor.
Under Applied Steps, select Source and check the file path. If a settings gear appears beside Source, click it to change the file location.
Select the updated text file in the Source settings. If there is no settings gear, open Home > Advanced Editor and replace the old path in the File.Contents expression, leaving the other import settings unchanged.
Select Home > Close & Load, then use Data > Refresh All. Check the loaded row count against the updated file.

Open Tab-Delimited Data with WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar interface. Open compatible Excel files and work with tab-delimited data as you move between office suites; it does not repair Excel Power Query connections.

Frequently Asked Questions
Why does Power Query show fewer rows at the Source step?
The query may still point to an older file. Open the query from Data > Show Queries, select Source under Applied Steps, and verify the file path.
Where can I change the text file path if Source has no gear icon?
In Power Query Editor, select Home > Advanced Editor and update the path inside the File.Contents expression. Keep the remaining import settings intact.
Does Power Query have a 50,000-row limit for text imports?
No. Power Query does not impose a 50,000-row limit for this import. If you load results to a worksheet, the worksheet limit is 1,048,576 rows, including a header.
Should the imported row count always match the file's line count?
Not necessarily. A header may be treated separately, and quoted fields can contain line breaks. Check the delimiter, header settings, and file contents if the path is correct but the counts still differ.




