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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
HOstatek
New Member

Creating new table with rows that differs

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 

 

 

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
HOstatek
New Member

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

Sorry, I meant Anti!

Oh well, it got it sorted for you 🙂



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors