Table Combine But to its respective Record/Row

[JustDavid]

I have 3 queries/fact tables - actuals, budgets and proforma.

Using FactTable_Actual as the "primary" and the other 2 tables as "secondary", how can I combine my budget and proforma tables values to Actuals to its respective record? In other words, for each record in tblActual of UID and CostCode, bring its values to the newly added column (column G and H respectively for tblBudget and tblProforma). Otherwise, "create" a new record.

e.g1 - tblProforma and/or tblBudget UID and CostCode is/are found in tblActual.

looking at row 4 of tblActual ([UID] = "A" and [CostCode] = "4001-01"), tblProforma has exact match (found in cell H5 for UID, cell I5 for CostCode). Thus bring in the value 53 (cell J5). The same for tblBudget where it has exact match (found in cell L5 for UID, cell M5 for CostCode). Thus bring in the value 87 (cell N5).

e.g2 - tblProforma and/or tblBudget UID and CostCode not found in tblActual.

in tblProforma, we can see that for UID=A where CostCode is 4001 or 9999 didn't exist in tblActual. The same with tblBudget, we can see that UID=A or B where CostCode is 4001, it didn't exist in tblActual. When this happen, creates a "new" record in tblActual

I hope my last screenshot that shows my desired result will explain clearly of what I desired to achieve.

I originally thought that using Table.Combine({tblActual, tblBudget, tblProforma}) would works, by renaming the 'Cost Code' in tblProforma and tblBudget to 'Cost Code Original'. But it didn't. Below is my original data with the incorrect output. I've highligted the color pair to where it needs it to be.

Combine Tables to respective CostCode.xlsx

[jgeddes · Accepted solution]

You can merge the budget table into the actual table with a full outer join using the UID and Cost Codes as the key for the join. Expand the budget table and then replace the nulls in the 'actual' UID column with the value from the 'budget' UID column. Do the same for the 'actual' cost code column nulls. Delete the 'budget' UID and Cost Code columns. Now repeat these steps for the Proforma table. I attached a copy of your .xlsx file with the power query code.