Join 2 Tables and replace values in the master table based on data in exception table

Here is an example that might help...
If you have two tables such as,

'masterTable'

'exceptionTable'

You can merge the 'exceptionTable' into the 'masterTable' using the ID and Date columns as the key.

Expand only the exceptionValue in the merged column.

Your table should now look something like...

Add a column that determines which table's value we will end up with.

Now select the 'exceptionValue' column and choose 'Replace Values' from the 'Transform' ribbon...
Use 'null' as the value to find and a random number as the 'Replace With' value. It does not matter what number as we are changing it right away.

The function value should populate with...

Replace the '99999' with 'each [masterValue] and you should end up with...

You can now change remove the masterValue column if you do not need it for other purposes, change data types and rename columns as desired to end up with...

Hope this helps.