Pivot Table combining two data sets

PQ method:

1. Load both to PQ (Get & Transform) as connection only.
2. Then add blank query with following as source.

Please Login or Register  to view this content.

3. Convert the resulting list to table and then change type to Text and rename to "Country".

This will serve as dimension table to tie two fact tables together.
Load all queries into data model.

Now in Data ribbon tool -> Data tools -> Relationships. New.

Pick original table prefixed with Data Model Table. Add it's country column as foreign.

Pick Data Model Table that was created from blank query as Related table, Country as Primary.
0.JPG

Do the same for the other original table.

Insert Pivot -> Use this workbook's Data Model.

Use column from Query1 (created from blank) as row label. And then add other columns from each respective table into value field.
1.JPG

Or you can create Country table in Excel sheet and use that as dimension table.

You are welcome

It's not that complicated really. It just looks that way.

In data model for Pivot Tables, or any sort of data analysis/reporting. You'd want dimension tables that connect different fact tables.
Most commonly Star Schema or Fact Constellation Schema (collection of Star Schema).

Dimensions are tables that hold descriptive attributes related to fact data and should be one to many relationship to fact table.

So in this case, unique list of country becomes the dimension table, which connects two separate tables based on it.

See link below for detailed explanation on Star Schema (your case is simple Fact Constellation Schema).
https://docs.microsoft.com/en-us/pow...ce/star-schema

I created the dimension table in PQ, but you can do the same in worksheet as well.