Lookup from multiple Data Tables using Cube Value in Excel

Question

I am looking to join multiple tables and create a database in excel using the cubevalue function however facing some issues. I have the sample data below.

As an example, I have two tables named "TableA" and "TableB" and would like to merge them into "TableC". I know I can use Index Match etc. combination but I am fairly new to PowerPivot and PowerQuery and was wondering if there is a quicker way to solve the problem using those

I used the function

=CUBEVALUE("PowerPivot Data",A2,B2,C2,"[Measures].[Sum of X]") 

but there are two problems I am facing.

1) I would like the "PowerPivot Data" to be dynamic and referred to the table name such as "TableA" or "TableB"

2) "[Measures].[Sum of X]" to be dynamic to the variable "X" and "Y"

I think if those two become dynamic then creating a database for me would be super quick. Maybe I am tackling the problem the wrong way?

Links to sample data below

TableA & TableB

TableC

Answer 1

I would tackle most of this in Power Query. I dont think any formulas or code are required.

Append Queries can stack the rows from TableA beneath the rows from TableB, with separate columns for X and Y.

Then I would select the X and Y columns, and use Transform / Unpivot Columns / Unpivot Only Selected Columns. That will twist the X and Y columns into Rows with combined Attribute ("X" or "Y") and Value (numbers from X & Y rows) columns.

From there you probably want to rename columns, then pump the result into Power Pivot for dynamic filtering and summing.