I have a system that is using PQ to import various CSV files. In those CSV files each row has a StaffID that uniquely identifies each member of staff. I have another sheet which has all current Staff details so I can match the import data to the existing staff by linking on the StaffID field.

If a new member of staff joins the company, I would only get notification as an unknown StaffID would appear in the CSV files I am importing. Is the correct approach here to either a) write a PQ to find StaffIDs in the new CSV files that don't match the master list in Excel or b) run the import into Excel and process those 'orphan' rows in Excel ie StaffID's in the new import that don't have a matching StaffID in the Master List Excel sheet?

I'm looking for any advice on 'best practice' here. I could solve this using only Excel / VBA but I am new to PQ so I and trying to find more about how it can be used and in what scenarios.

Thanks in advance for any feedback.

Expected result: Advice on a 'best practice' or sensible approach to use Excel and PQ to manage the handling of 'orphaned' or data without a matching PK.

CodePudding user response：

The best approach to finding deltas like this is a simple outer join and then remove the matching rows. It will highlight rows which exist on the left side but don't exist on the right and vice versa. You can then load that to a table either in Excel or PowerBI with an alert.