Power Query Problems

Fix Power Query Adding Unexpected Rows During Refresh

Ayan Masood Sep 30, 2026 868 views

Question details

The user needs to prevent Power Query from generating extra rows and displacing column values when refreshing a dataset.

How to Fix Power Query Adding Unexpected Rows During Refresh

Product: Microsoft Excel
Device & OS: not provided
Scenario: Refreshing a data query where the source file contains cells with multiline text (carriage returns or line breaks).
Observed behavior: Power Query treats carriage returns within single cells as row delimiters, splitting the text and creating entirely new, misaligned rows in the output table.

Before you start

Verify your raw source data format (such as CSV or an Excel worksheet) to identify cells that contain multiline text or intentional line breaks.

Solution 1Recommended

Replace Carriage Returns in the Source Data

The most effective way to prevent Power Query from splitting rows is to remove or substitute line breaks in the source file before loading the data.

Power Query's data import engine often reads standard carriage returns as the end of a record. By sanitizing the source document first, you ensure the query loads exactly the number of rows intended.

Open your source data file

Open the original dataset (Excel or CSV) that Power Query is pulling from.

Open Find and Replace

Press the keyboard shortcut 'Ctrl + H' to open the Find and Replace dialog box.

Insert a carriage return in the search field

Click into the 'Find what' box and press 'Ctrl + J'. You will not see a character appear, but a tiny blinking dot indicates the line break has been registered.

Replace with a separator character

In the 'Replace with' box, type a space, a comma, or a custom separator (like '||') that you can later use to restore line breaks if necessary, then click 'Replace All'.

Save and refresh Power Query

Save your source file, navigate back to your main workbook, and click 'Refresh All' on the Data tab. The unexpected rows should no longer appear.

Replace Carriage Returns in the Source Data

Reinstating Line Breaks: If you used a custom separator (like '||'), you can safely replace it back with line breaks directly within the Power Query Editor using the 'Replace Values' feature, choosing to replace '||' with '#(lf)'.

Solution 2

Adjust Power Query Import Settings for CSV Files

If you are importing data from a CSV file, you can modify the Advanced Editor settings to properly parse line breaks enclosed in quotes.

Free Microsoft Office alternative

Handle Data Imports Seamlessly with WPS Spreadsheet

If Microsoft Excel's data transformation features are creating unexpected errors, consider trying WPS Office. It provides an intuitive, lightweight, and highly compatible spreadsheet tool that imports CSV files and manages text data smoothly without complex query configurations.

1. Download and install WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
2. Open your dataset in WPS Spreadsheet: Launch WPS Spreadsheet and go to 'Menu' > 'Open' to select your raw Excel or CSV file.
3. Import data using the wizard: If opening a CSV, the built-in Text Import Wizard will automatically launch, allowing you to correctly define text qualifiers to keep multiline text in a single row.

Highly compatible with Microsoft Excel formats (.xlsx, .csv, .txt)Intuitive Text-to-Columns and data import wizards that easily handle line breaksFree, lightweight, and fast alternative for daily data processingFamiliar interface requires zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does a line break create a new row in Power Query?

By default, data import engines treat carriage returns or line feeds as row delimiters (the end of a record). Unless the text is properly encapsulated in quotes and the import settings recognize that quote style, Power Query will split the data at the line break.

How do I find hidden carriage returns in Excel?

You can use the 'Find and Replace' feature. Press Ctrl+H, click into the 'Find what' field, and press Ctrl+J. This inputs the hidden carriage return character, allowing you to search for all instances in your worksheet.

Can I put the line breaks back into Power Query after cleaning the source?

Yes. If you replaced line breaks with a unique placeholder in the source file, you can load the data into Power Query, select the column, choose 'Replace Values', and replace your placeholder with Power Query's line feed character notation: #(lf).

Excel & Data Problems