Forum Discussion

JustDavid's avatar
JustDavid
Icon for Helper V rankHelper V
2 years ago
Solved

Table Combine But to its respective Record/Row

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...
Need Help
Show and Tell
  • jgeddes's avatar
    jgeddes
    2 years ago

    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.

jgeddes's avatar
jgeddes
Icon for Super User rankSuper User
2 years ago

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.