I'm a little confused by your example. If you could post examples of table A and table B along with your join type and conditions it would help me and forum troubleshoot.
Below is a screen shot of what I am looking at once I have joined. This is one line for an account. The asset Bucket is a group of system and the system type is the individual system name with the product s just another description. The issue I have is the numbers are in one sheet and the products are related to the system across the asset bucket field and this is then duplicating the total for the account bucket line on each system type line. Would like the amount to only show once on the system line. Would like to send work book but not possible. Will see if I can replicate using the superstore data.
If I understand correctly, try creating a calculated field that would consolidate your $A, $B, $C fields and using it on your Marks shelf instead of all three. I attached an example using your table in the original post. I renamed $A, $B, $C as Field1, Field2, Field3 and then created the following calculated field.
Calculated Field: Field Consolidation
if not ISNULL([Field1]) then [Field1]
ELSEIF not ISNULL([Field2]) then [Field2]
ELSEIF not ISNULL([Field3]) then [Field3]
Hopefully this helps, otherwise I'll wait for the superstore example. You could also create a few rows of test data similar to your production data in excel and package the workbook. If you're joining two tables, make sure to use two sheets in excel so it can be replicated properly.
Field Consolidation Example.twbx 13.4 KB