Power Query Problems

How to Remove Duplicate Rows in Excel Power Query Imports

Camila Milosovich Oct 20, 2026 869 views

Question details

The user needs to eliminate duplicate rows that appear after importing an Excel workbook into Power Query.

How to Remove Duplicate Rows in a Power Query Import

Product: Excel Power Query
Device & OS: not provided
Scenario: Importing data from an Excel workbook into Power Query where the source contains both sheet data and a named range.
Observed behavior: Power Query displays duplicate records for the same data—one from the worksheet and one from the named range, sometimes with incomplete fields.

Before you start

Ensure you have access to the Power Query Editor and verify whether your source Excel file contains any defined Named Ranges alongside your standard table data.

Solution 1Recommended

Filter Out Named Ranges in Power Query Editor

The most common cause of duplicate rows in workbook imports is Power Query loading both the Sheet and the Named Range. Filtering out the Named Range resolves this.

When importing a workbook, Power Query reads all structural elements. If a worksheet also contains a Named Range covering the same data, both will be imported as separate entities during the initial source step.

Open Power Query Editor

In Excel, navigate to the 'Data' tab and select 'Get Data' > 'Launch Power Query Editor', or double-click your existing query in the Queries & Connections pane.

Locate the Source Step

In the 'Applied Steps' pane on the right side of the window, click on the 'Source' or 'Navigation' step to view the initially loaded items before any transformations.

Filter the Item Kind

Look for the column named 'Kind' (or 'Item'). You will see values such as 'Sheet' and 'DefinedName' (or 'Named Range'). Click the filter dropdown arrow on this column header.

Deselect Named Ranges

Uncheck the 'DefinedName' or 'Named Range' option so that only 'Sheet' or 'Table' remains selected, then click 'OK' to apply the filter.

Filter Out Named Ranges in Power Query Editor

Duplicates Prevented: This action ensures Power Query only reads from the primary worksheet or table, preventing duplicate or incomplete records from being passed to the next steps.

Solution 2

Review Query Code in the Advanced Editor

If filtering the initial steps doesn't visually resolve the issue, you can manually inspect the M code to identify where duplicates are introduced.

Free Microsoft Office alternative

Looking for a Lighter Alternative to Complex Excel Queries?

If dealing with Power Query errors and duplicate named ranges is slowing you down, try WPS Office. It offers a clean, lightweight spreadsheet experience with robust, built-in data deduplication tools that don't require complex query coding.

1. Open your file in WPS Office: Launch WPS Spreadsheet and open your existing Excel workbook.
2. Select your data: Highlight the range of cells or the entire table that contains the data you want to clean.
3. Remove Duplicates: Go to the Data tab and click on 'Remove Duplicates' to easily clean your dataset without entering a query editor.

Easily remove duplicates with built-in data tools in just one click.Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats.Lightweight application that runs smoothly on older and newer devices.Free to use with a familiar, easy-to-navigate interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query show duplicates when my source file has none?

Power Query reads the structural elements of an Excel file. If your data is in a worksheet and also defined as a Named Range or Table, Power Query may import both entities as separate rows in the initial source step, making it look like your data is duplicated.

Can I use the 'Remove Duplicates' button instead of filtering named ranges?

While you can use the 'Remove Duplicates' feature on the Home tab of Power Query, it is better practice to filter out the Named Range at the Source step. This prevents incomplete records from the named range from being mistakenly kept as the primary data.

What is a Named Range in Excel and how does it affect queries?

A Named Range is a feature in Excel that allows you to assign a specific name to a cell or a range of cells. This makes formulas easier to read but can cause duplicate data loads in Power Query because the query engine sees the Worksheet and the Named Range as two separate data sources.

Excel & Data Problems