I have 2 tables that are joined together via a concatenation of unit/account which is what is going to be shown in a table. The goal is to show variances in 2 different data sources that should match on a unit/account level. The problem is if a new account or unit show up on one table that is not on the other it will cause issues on whichever table is not chosen as the items on the table. I've looked into merge queries and append queries but both seem to have their issues in this scenario. The goal would be to have the equivalent of an SQL union between two tables ie:
SELECT unit, account, Sum(amount)
FROM table1
GROUP BY unit, account
UNION
SELECT unit, account, Sum(amount)
FROM table2
GROUP BY unit, account
Firstly, you need to create a bridging table in PQ which will contain all the combined instances of unit and account from t1 and t2 and then you can perform a distinct on that table so that it only contains all unique combinations. You can create the key on that table. Then you can create a relationship with that bridging table and t1 and t2 based on the key.
Once you have that set up, bring the axis from the bridging table in the viz and perform sum on t1 and t2.