Power Query Problems

Combine Excel Columns, Remove Duplicates, and Preserve Order with Power Query

Steve KSteve K Nov 14, 2026 868 views

Question details

Combine two timetable columns into one ordered list, removing duplicates and updating the result when source data changes.

Combine Excel Columns, Remove Duplicates, and Preserve Order with Power Query
Product
Microsoft Excel 365 Power Query
Device & OS
not provided
Scenario
The user needs a duplicate-free timetable list as part of a larger procedure.
Observed behavior
The desired result retains the first occurrence of each value in the chosen column order and can be refreshed from the source data.
Before you start

Arrange the source rows in the order you want to preserve. Check that the first column should be processed before the second, and note the column headers for use in the query.

Solution 1Recommended

Combine the columns with an ordered Power Query

Use a query that processes the first column from top to bottom, then the second, retaining the first occurrence of each value.

This approach avoids formulas and VBA. The query preserves the source order at refresh time; it does not sort timetable values chronologically.

The query treats a blank value as a value, so a blank in the source can appear once in the result.

1
Create the source table

Select the two columns and their headers, choose Insert > Table, and confirm that the table has headers. On the Table Design tab, name the table Timetable.

2
Open a blank query

Choose Data > Get Data > From Other Sources > Blank Query, then select Home > Advanced Editor.

3
Enter the query

Replace the editor contents with the following code. Change Column1 and Column2 to your exact table headers, keeping them in the order you want processed: let Source = Excel.CurrentWorkbook(){[Name="Timetable"]}[Content], Combined = List.Combine({Table.Column(Source, "Column1"), Table.Column(Source, "Column2")}), UniqueInOrder = List.Accumulate(Combined, {}, (result, value) => if List.Contains(result, value) then result else result & {value}), Output = Table.FromList(UniqueInOrder, Splitter.SplitByNothing(), {"Timetable"}) in Output

4
Load the result

Select Done, then Home > Close & Load to place the combined list in the workbook.

5
Configure refresh

In Data > Queries & Connections, right-click the query and open Properties. Where available, enable refresh when opening the file or refresh at a chosen interval. You can also use Data > Refresh All after changing the source.

Refresh is required: The output does not update instantly on every source edit. Open-file or timed refresh can reduce manual work, but a larger procedure may need its own supported refresh trigger.
Free Microsoft Office alternative

Work with Excel Timetables in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for opening and editing Excel workbooks. You can move existing timetable files into WPS, although it does not fix Excel Power Query issues or guarantee that queries will refresh.

Open and edit Microsoft Excel XLSX and XLS filesWork with timetable columns in a familiar spreadsheet interfaceMove existing Office documents into WPS without starting from scratchUse a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Which column appears first in the combined list?

The query processes Column1 from top to bottom, followed by Column2. Swap their names inside List.Combine in the Advanced Editor to reverse that order.

Will the list update immediately when I edit a source cell?

No. Use Data > Refresh All, or open the query's Properties under Data > Queries & Connections to configure available open-file or timed refresh options.

Can I exclude blank values from the result?

Yes. In the Advanced Editor, filter the combined list before List.Accumulate. For example, replace Combined with List.Select(Combined, each _ <> null and _ <> "") in the List.Accumulate line.

Why does the query say it cannot find a column or table?

Check the table name on the Table Design tab and compare the column headers with the names in Table.Column. Update Timetable, Column1, and Column2 in the Advanced Editor to match the workbook exactly.