Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
GMadd
Helper I
Helper I

Append Tables as New No Duplicates Three Unique Columns

I have Three tables "ShippedDel24", "ShippedDel23" and "DailyMonth". 

 

ShippedDel24 is all deliveries that shipped year to date in 2024.

ShippedDel23 is all deliveries that shipped in 2023

DailyMonth is a report that is run daily for the current month. It is a month to date type report.

All of these reports have the same headers with about 25 different columns.

When I try to Append these tables together I am getting duplicate values for the current month. The issue is the current month that is in ShippedDel24 and the DailyMonth report have some of the same data. I cannot change how the DailyMonth report is run. So, I am wanting yo use Power Query to Append the reports and remove any duplicates. 

One of the issues is my deliveries have more than one item shipped to them but three of the columns and be used in each row to determine if they're duplicated or not.  

The columns are Delivery, Material, and Item but when I try to tell it to remove duplicates from these columns it doesn't work. 

= Table.Distinct(Source, {"Delivery", "Material", "Item"})

All of my columns are the same data type.

Also, for some reason when I append my Queries as New it duplicates two of my columns and enters a null value in each row for those two columns.

Please help

 

Thanks

I am not sure if "Appending Queries" is the corrent way to try and do what I am doing or if there is anoter way.  

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

This should work but only if you do it exactly in this order and if those three columns are truly your primary key.

 

= Table.Distinct(DailyMonth & ShippedDel24 & ShippedDel23 , {"Delivery", "Material", "Item"})

 

Also, for some reason when I append my Queries as New it duplicates two of my columns and enters a null value in each row for those two columns.

Power Query is case sensitive.  You have spelling differences or trailing spaces.

View solution in original post

all my column headings are the same

I would respectfully like to challenge you on that. If Power Query produces extra columns then there must be differences.  Please show all column headers in their natural habitat.

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

This should work but only if you do it exactly in this order and if those three columns are truly your primary key.

 

= Table.Distinct(DailyMonth & ShippedDel24 & ShippedDel23 , {"Delivery", "Material", "Item"})

 

Also, for some reason when I append my Queries as New it duplicates two of my columns and enters a null value in each row for those two columns.

Power Query is case sensitive.  You have spelling differences or trailing spaces.

Yes the three columns are my primary key. Is there anything special I need to do to set them primary? Also, all my column headings are the same. Is there any other reason why it would add these columns?

all my column headings are the same

I would respectfully like to challenge you on that. If Power Query produces extra columns then there must be differences.  Please show all column headers in their natural habitat.

I will check them in the morning. 
Tgank you 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.