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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
kbuckvol
Advocate I
Advocate I

Append tables: remove rows in first table if value appears in second table

I have two tables that I want to do an append query, but I want those rows from the second table that have the same "Item" to replace the rows from the first table with the same "Item" name.  I figure there is a round about way to do that by adding a column and indicating if there are duplicate "Item" names so that I can filter out the rows from the first table, but I feel like there's a more elegant way to do this!

 

Here is an example of the two sample tables and the results I'm looking for:

 

 

kbuckvol_0-1624045841954.png

 

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi @kbuckvol ,

Just use the following dax to create a new table could more elegant way to do this,and get the final want you want!

 

base data:

Table1:

vluwangmsft_0-1624352975316.png

 

Table2:

vluwangmsft_1-1624353000131.png

 

Dax about create new table:

Table = 
VAR TABLE11 =
    CALCULATETABLE (
        Table1,
        EXCEPT ( VALUES ( Table1[Item] ), VALUES ( Table2[Item] ) )
    )
VAR TABLE12 =
    ADDCOLUMNS ( TABLE11, "Table", "Table1" )
VAR table22 =
    ADDCOLUMNS ( Table2, "Table", "Table2" )
VAR TABLEall =
    UNION ( TABLE12, Table22 )
RETURN
    TABLEall

 

Final result:

vluwangmsft_2-1624353052383.png

 

 

Wish it is helpful for you!

 

Best Regards

Lucien

View solution in original post

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

Hi @kbuckvol ,

Just use the following dax to create a new table could more elegant way to do this,and get the final want you want!

 

base data:

Table1:

vluwangmsft_0-1624352975316.png

 

Table2:

vluwangmsft_1-1624353000131.png

 

Dax about create new table:

Table = 
VAR TABLE11 =
    CALCULATETABLE (
        Table1,
        EXCEPT ( VALUES ( Table1[Item] ), VALUES ( Table2[Item] ) )
    )
VAR TABLE12 =
    ADDCOLUMNS ( TABLE11, "Table", "Table1" )
VAR table22 =
    ADDCOLUMNS ( Table2, "Table", "Table2" )
VAR TABLEall =
    UNION ( TABLE12, Table22 )
RETURN
    TABLEall

 

Final result:

vluwangmsft_2-1624353052383.png

 

 

Wish it is helpful for you!

 

Best Regards

Lucien

Holy guacamole, this is perfect!  Just what I was hoping for 🙂

 

One thing to note for those of you that might have this same question, I actually had a lot more columns in my tables and found out the UNION function requires the fields of both tables to be in the same order.  Of course, both my tables had the fields in differing orders but this post that describes the SELECTCOLUMNS function to solve that problem.

 

https://community.powerbi.com/t5/Desktop/Reordering-Table-Columns-in-DAX-Outside-of-Query-Manager/m-...

TomMartens
Super User
Super User

Hey @kbuckvol ,

 

in this article Using Power Query for Data Sampling - Mincing Data - Gain Insight from Data (minceddata.info) I mention a trick to create a index column inside a group (here your item), meaning a simple rank.

 

First create a column that contains the table number, append the tables, create the index column use the column that contains the table number as sort column. This approach assumes that each table just contains just a single instance of the item. If a table can contain more than one instance then you have to use a little more sophisticated algorithm.

 

Filter the table accordingly - done.

 

Hopefully, this provides some ideas on how to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.