Power BI Power Query Append Two table Distinct Values Only Based on two columns each row

[GMadd]

I have two tables I need to append in excel files.

The first table called "Main" and the file name is called "Yearly Shipping". This file contains data for everything shipped year to date. 

The second table is called "MtoD" and the file name is called "Monthly Shipping".

The ERP I am using gives me a report daily that is always month to date. Meaning I will get all shipping data from August 1st through August 20th (today is August 20th). Tomorrow I will get the report and it will be from August 1st through August 21st and so on down the road. I want power query to remove duplicates based on two columns in each row. These two columns are called "Delivery" and "Material". If another row has the same delivery number and material number I do not want to add it to the table called "Main". I need to know how to write M-code to state I only want to see distinct rows and I am struggling on how to do this. Below is a sample of my "Main" table and "MtoD" table and what I want the "Main" tabl

	Main Report
Delivery	Act. Gds Mvmnt Date	Material
88137521	7/30/2024	23461BIO
88137522	7/30/2024	22852BIO
88137522	7/30/2024	23642BIO
88130535	19-Aug-24	36112CLX
88130535	19-Aug-24	36106CLX
88136019	19-Aug-24	29005CLX
88136019	19-Aug-24	12005CLX
88136019	19-Aug-24	50006ACL
88142876	19-Aug-24	23505PTM
88142876	19-Aug-24	23504PTM
88142876	19-Aug-24	23606PTM
	MtoD
Delivery	Act. Gds Mvmnt Date	Material
88130535	19-Aug-24	36112CLX
88130535	19-Aug-24	36106CLX
88136019	19-Aug-24	29005CLX
88136019	19-Aug-24	12005CLX
88136019	19-Aug-24	50006ACL
88142876	19-Aug-24	23505PTM
88142876	19-Aug-24	23504PTM
88142876	19-Aug-24	23606PTM
88130535	19-Aug-25	59232CLX
88130535	19-Aug-25	11022CSP
	End Result (Appended Table)
Delivery	Act. Gds Mvmnt Date	Material
88137521	7/30/2024	23461BIO
88137522	7/30/2024	22852BIO
88137522	7/30/2024	23642BIO
88130535	19-Aug-24	36112CLX
88130535	19-Aug-24	36106CLX
88136019	19-Aug-24	29005CLX
88136019	19-Aug-24	12005CLX
88136019	19-Aug-24	50006ACL
88142876	19-Aug-24	23505PTM
88142876	19-Aug-24	23504PTM
88142876	19-Aug-24	23606PTM
88130535	19-Aug-25	59232CLX
88130535	19-Aug-25	11022CSP

[dataflip]

The most efficient way to handle this scenario is by using Power Query's anti-join logic to prevent duplicate records when appending the "MtoD" table to the "Main" table. Since both tables contain overlapping data based on the columns "Delivery" and "Material", the first step is to create a composite key in each table by combining these two columns (e.g., Delivery-Material).

This unique key allows you to easily identify duplicates. Once the keys are created, you can use a List.Contains function or a left anti-join to filter the "MtoD" table, keeping only the records that do not exist in the "Main" table.

After filtering, you append the new unique rows from "MtoD" to "Main" to create the final combined dataset. This approach ensures that only new shipping records are added each day, avoiding duplication of historical data. The solution is scalable, works well with daily ERP updates, and keeps your Year-to-Date (YTD) shipping data accurate and clean.

[wckeneson]

Here is the code that I was able to solve this problem with (Thank you COPILOT)
DistinctTable = DISTINCT( UNION( SELECTCOLUMNS(Table1, "ColumnName", Table1[Column1]), SELECTCOLUMNS(Table2, "ColumnName", Table2[Column2]) ) )

[jgeddes]

Does this work for you?

Table.Distinct(Table.Combine({mainTable, mtdTable}))

[GMadd]

Thanks for the help! I should have said I am very new to writing these formulas; with that said where do I add this into the append formlua below?

let
Source = Excel.Workbook(File.Contents("C:\Users\gmadden\Yearly Shipping.xlsx"), null, true),
Main_Sheet = Source{[Item="Main",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Main_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Delivery", Int64.Type}, {"Plant", type text}, {"Created On", type date}, {"Act. Gds Mvmnt Date", type date}, {"Goods Issue Date", type date}, {"Name of the ship-to party", type text}, {"Location of the ship-to party", type text}, {"Region", type text}, {"Postal Code", type text}, {"Incoterms", type text}, {"Material", type text}, {"Description", type text}, {"Actual delivery qty", Int64.Type}, {"Base Unit of Measure", type text}, {"Net weight", type number}, {"Weight Unit", type text}, {"Shipping Conditions", Int64.Type}, {"Route", type text}, {"Created By", type text}, {"Reference Document", Int64.Type}, {"Total Pallets", Int64.Type}, {"Total Weight", type number}, {"Ship-To Party", Int64.Type}, {"Purchase Order Number", type text}, {"TU partner", type text}, {"Otm Shipment Number", Int64.Type}, {"Sold-To Party", Int64.Type}, {"Name of sold-to party", type text}, {"Overall pick.status", type text}, {"Overall WM status", type text}, {"Container/Trailer/PRO Number", type text}, {"Pick up Appt Date", type date}, {"Pick up Appt Time", type datetime}, {"Rush Order Flag", type text}, {"Delay Reason Code - Header", type text}, {"Profit Center", Int64.Type}, {"Net Value", type number}, {"Time Created", type datetime}, {"Carrier Assigned Date", type date}}),
#"Appended Query" = Table.Combine({#"Changed Type", MtoD})
in
#"Appended Query"

[jgeddes · Accepted solution]

No worries. You can change the line that is currently...

#"Appended Query" = Table.Combine({#"Changed Type", MtoD})

to

#"Appended Query" = Table.Distinct(Table.Combine({#"Changed Type", MtoD}))

The #"Changed Type" replaces the 'mainTable' in what I initially wrote while the MtoD replaces the 'mtdTable'.

[GMadd]

Thank you for your help! This worked perfectly.