Is it possible to compare two tables to show any additions and deletes?

[Anonymous]

**Updated with more details on the steps I take outside of Power BI/Power Query

Hi All,

I have a report that I need to run monthly to compare for any differences.  Both tables have the same column headers, but items (UPC's) could have been added or delelted to different stores.

I would like to combine the two tables (Current POG & Prior POG) to capture the differences of what was added or deleted.

I've researched for awhile now, and it seems everything I find is just to show the differences in a new table, or to get rid of the data that doesn't match in the 2nd table.  I need both tables to Merge & Append...

Data Examples:

Prior POG Example (Last Month's report):

Current POG Example (This month's report):

The data I ran this month combined multiple reports, formatted, and a table created in Power BI.

Here is my Relationship Model: 

The Master Store List, Planogram Facing Grid, Planogram Store Mapping, UDF Item # are all used to create the Current POG table.

When I imported the Prior POG csv file, Power Bi automatically connected.  (I don't know if this needs changed or not.)

In Excel (before using Power Bi/Power Query):

1. I would perform a SUMIFS on the Prior POG Table:

=SUMIFS('Current POG'!H2:H10,'Current POG'!$C$2:$C$10,C2,'Current POG'!$E$2:$E$10,E2)

2. Whatever was missing from the Current Pog Table, I would add to the bottom of the Current POG Table (Append)

(This is to make sure All Items (from both tables) are captured.)

3. I would Add a 0 (False) in the Current POG Column (Merge?)

4. On the Current POG Table, Add a Column title (Prior POG), and perform a SUMIFS (Merge?)

=SUMIFS('Prior POG'!$H$2:$H$13,'Prior POG'!$C$2:$C$13,C2,'Prior POG'!$E$2:$E$13,E2)

Final Table (Results Needed)

 

[parry2k]

@Anonymous lot of this needs to be done in the Power Query instead of DAX.  Do you want the current table to show all the rows and tag records as delete or new? What are your expectations from the final table?

[Anonymous]

@parry2k

I provided an example for the Final Table that I need in my orignal post: Final Combined Table Example 

Are you referring to Power Query in Excel?  

I went through the whole process of the power Query to create the Current POG table.  I even created bridging tables for the many to many relationships. 

I start the Pivot Table, and the 3rd Column gave me an error:

The PivotTable report will not fit on the sheet.  Do you want to show as much as possible.