Power Query - Get new or updated rows after overwriting exception values from one table to another

[Anonymous]

Hi,

I have 3 tables:

Mappings:

SFReport:

StudyReport:

Current Process (manual) :

- Analysts usually extract a list of unique Manager names and Team names from the `SFReport table` and append it below Mappings table.

- They then manually assign the Users to these Managers in the User column.

- They then add any exceptions in the Exception column for any `Manager - User` combination working on Accounts - the User needs to be replaced for that specific Account entered in exceptions. The format to determine which Accounts worked by which User is as e.g. `Acct1|Vincy,Acct2,Renu` i.e. `<Account name | User name>, <Account name | User name>` etc.

- This Mappings table is then merged back to the `SFReport table`, which should be a connection-only table that pulls external data into the query.

- The merge basically fills the `User` column with by looking up the Manager column

- It then checks the Accounts column of the `SFReport table` and sees if there are any exceptions for any `Manager - User` combination for that Account, then replaces the original User with the one given in exception.

e.g. If `Acct1 & Sidra - Zara` then change User Zara to Vincy`

N.B: This is only for new rows of `SFReport table` that may have that combination and not replace for earlier ones.

- Finally the `SFReport table` merges with the `StudyReport table` and dumps only new or updated rows to the `StudyReport table`.

Please note there may be multiple columns in between the columns show in the below Sample file in the StudyReport table.

Sample New File:

It contains all the tables & instructions for each table.

Can someone help me such an implementation? Any help will be most appreciated.

@AlexisOlson , @MarcelBeug @ImkeF 

[Anonymous]

@AlexisOlson hope you are doing fine.

Would you be able to help with the 1st part (as per the title of my post) i.e. self-referencing Mappings file and including the exceptions in the StudyReport? Regarding updated or new studies, you already showed me how to do it. So i would be able to do the latter part. Thanks in advance.

[AlexisOlson]

Sorry. This is a bit beyond the scope of question and answer for me.

[Anonymous]

Can anyone help with the 1st scenario i.e. Mappings as a self-referencing table that doesnt change the sort order of exceptions + updating those exceptions user names to the correct accounts based on the Manager - User combination?