Power Query技术问询：无唯一ID列下实现FYTD LY与FULL FY LY数据表的无重复合并

Absolutely doable in Power Query—even without unique ID columns! The key is using left anti joins to isolate rows from Table 2 that don’t exist in Table 1, then combining that subset with Table 1’s full data. Here’s a step-by-step breakdown tailored to your needs:

Step 1: Add a "Data Source" identifier column to both tables #

First, we need to tag each row so we can track where it originated:

• Load Table 1 (FYTD LY) into the Power Query Editor.
• Navigate to the Add Column tab → click Custom Column.
• Name the column Data Source and paste this formula in the editor:

  "FYTD LY"
  

• Repeat this process for Table 2 (FULL FY LY), but use this formula instead:

  "FULL FY LY"
  

• Save both modified tables (renaming them to Table1_WithSource and Table2_WithSource can help keep things organized).

Step 2: Extract only unique rows from Table 2 (not present in Table 1) #

We’ll use a left anti join to filter Table 2 down to rows that have no exact matches in Table 1:

• Go to the Home tab → click Merge Queries → select Merge Queries as New Query.
• In the merge window:
  • Pick Table2_WithSource as the first table.
  • Pick Table1_WithSource as the second table.
  • Hold Ctrl and click every column in both tables (since we have no unique ID, this tells Power Query to match rows based on all column values).
  • Under Join Kind, select Left Anti (rows only in first table).
• Click OK. You’ll get a new query containing only Table 2 rows that don’t duplicate any rows in Table 1.

Step 3: Combine Table 1 with the filtered Table 2 subset #

Now we’ll append the two datasets to create your final merged table:

• Select Table1_WithSource in the Queries pane.
• Go to the Home tab → click Append Queries → select Append Two Tables.
• In the append window, choose the filtered Table 2 query (from Step 2) as the second table.
• Click OK.

Final Outcome #

Your merged table will include:

• Every row from Table 1 (FYTD LY), tagged with Data Source = "FYTD LY"
• Only the unique rows from Table 2 (FULL FY LY) that don’t exist in Table 1, tagged with Data Source = "FULL FY LY"
• Zero duplicate rows, since we excluded Table 2’s matches via the left anti join

内容的提问来源于stack exchange，提问作者Sound