Power Query: Appending tables without creating duplicates

Question

I have two tables.

• Table 1 contains sales data on 'Financial year to date last year' (FYTD LY)
• Table 2 contains sales data on 'Full financial year last year' (FULL FY LY)

Using Power Query:

I want to append these tables into one table, with a column indicating which of the two tables the data came from.

I don't want duplicate values in the appended table -So I want to keep all the data from 'FYTD LY' and only combining it with the data from 'FULL FY LY' that doesn't already exists inside the 'FYTD LY' table.

Unfortunately my tables don't contain an ID column and I can't create one (that I can be certain will stay unique) from the columns available.

Is this even possible in Power Query -and how would I go about it?

Answer 1

See if this helps you

Simplest method .... load in your first table to powerquery, file close and load.

Load in your second table to powerquery.

Add column ... custom column .. with formula ="Second table"

Home .. append queries .. append the other table

Click select all the columns except the custom column, right click and remove duplicates

done.

Full code for Table2 (assuming you already created query Table1 with data from Table1) :

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "From", each "Second Table"),
#"Added Index" = Table.Combine({#"Added Custom",Table1}),
#"Removed Duplicates" = Table.Distinct(#"Added Index", {"a", "b", "c"})
in  #"Removed Duplicates"

If you want to get fancy, change the formula to replace Table1 so

= Table.Combine({#"Added Custom",Table1}),

becomes

= Table.Combine({#"Added Custom",Table.AddColumn(Table1, "From", each "First Table")}),