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

[kbuckvol]

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:

[v-luwang-msft · Accepted solution]

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:

Table2:

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:

Wish it is helpful for you!

Best Regards

Lucien

[kbuckvol]

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]

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