Creating new table with rows that differs

[HOstatek]

Hi to everyone. 

I have two tables.

Table1 is consisting of datas from ERP system:

Employee name | Function | Employment status | Actual Location | Actual HALL | Shift Profile | Actual HC

Table2 is consisting of Datas from Excel file that is filled each week by Hall Coordinators. - if workers for example shift need to be change - hall coordinator is changing it in excell file published on company portal.

Employee name | Function | Employment status | Actual Location | Actual HALL | Shift Profile | Actual HC

Tables are the same. I have both in Power Query.

Each friday HR worker need to see report in PowerBI (Table3) that will show only rows that differs between Table1 and Table2. So he can change for example shift of the workers in the ERP system - the one that hall coordinator change during the week in excell file.

So could someone please help me to build a table that will be consisting of rows that differs between Table1 and Table2. 

Employee name column consists of the same names in both tables.

Regards,

Hubert 

[HOstatek]

Dirty or not is working. Only except of Right Outer I used Left Anti. Thank You for help! 

[BA_Pete]

Sorry, I meant Anti!

Oh well, it got it sorted for you 🙂

[BA_Pete · Accepted solution]

Hi @HOstatek ,

I am assuming that the Excel file that the Hall Coordinators use is a data dump of the original ERP data that they then overtype adjustments onto. If that's not the case, then this won't work.

A quick-and-dirty way:

1) Select your ERP table and go to Home tab > Merge Queries > Merge Queries as New

2) In the top of the merge dialog, select your first column, then hold down shift and select your last column.

3) In the bottom of the dialog, select your Excel table, then select all columns the same as you did before (columns must be in EXACTLY the same order).

4) At the bottom of the dialog, change the merge type to Right Outer. This will leave only rows that don't match to the Excel record.

5) When you expand the merge, ensure you check 'Use original column name as prefix' to get friendly names instead of 'Employee Name.2' etc.

Pete