Linking two pivot tables

[BittenApple]

Hello team,
I have two sets of data and I want to insert pivot tables. These two sets of data have common fields and I wonder if there is an approach to join these two sets through a pivot table.

Because in a pivot table field selection says: more tables. Having said that it means we can join data from a couple of place into each other.

please advise me.

Regards,
Bittenapple

[HansV]

Does this help? Pivot Table Multiple Consolidation Ranges

[p45cal]

Yes, you can do this by using the Data Model (or with Power Query)
Best attach a workbook with the source data tables and some idea of the pivot tables you want.

[BittenApple]

Thanks fir the response. Are data model or power quer two different things?
Respectfully,
BittenApple

[HansV]

See Create a Data Model in Excel

[p45cal]

Are data model or power quer two different things?

Yes.

[BittenApple]

Hello team,
Since you requested data, I have attached a file. I will go ahead and read the link, Thanks for it.
BittenApple

[BittenApple]

Hello team,
Since you requested data, I have attached a file, which I want to join two pivot tables. I will go ahead and read the link, Thanks for it.
BittenApple

[BittenApple]

Hello team,
Is it like to say data model is as joining tables by linking through common fields and power query is as query?
Thanks,
BittenApple

[p45cal]

Is it like to say data model is as joining tables by linking through common fields and power query is as query?

I think so, although a query can just append the tables.
The data transformed by Power Query can be added to the data model but it isn't needed.
Using the data model by itself to join the tables proved more difficult than I thought (I had expected to be able to link the 2 tables easily using only the pivot table user interface); it insisted on a one-to-many realtionship which looked like I'd have to create another table of unique keys. I tried using a UNION in DAX but I'm not familiar enough with it to make it work.
So I ended up using Power Query which was straightforward.
In the attached, I've assumed the only common field is Plan.
There is one query (called Appended) which joins the 2 tables, and the result is loaded directly to the pivot table at cell I9 of the Lab sheet..
It's a regular pivot table. The Data Model is not involved.
This query is written to include any number of tables in the workbook, however it restricts the tables it uses to those you name beginning with zz. For the join(s) to be successful, the fields (headers) which you want to be appended must be the same (including upper/lower case, and no trailing/leading spaces differences). If there are new fields that you want to add, the last step in the query (Expanded Content) would need to be deleted and re-done (2 clicks).
So if you change the data you want to pivot, you need only refresh the pivot.

[p45cal]

If there are new fields that you want to add, the last step in the query (Expanded Content) would need to be deleted and re-done (2 clicks).

This can be a bit of a pain, so I've tweaked the query so that you don't need to re-do any steps. On refreshing the pivot table any new fields will appear in the pivot table fields.

[p45cal]

…and just for the sake of completeness the attached contains 3 queries, each only one step. 2 of them only load the data from a table, the 3rd combines (appends) the other 2.

[BittenApple]

Hello p45cal,

Can you please elaborate what you have done, step by step?

Hello Hans,

Referring to: Post 1423, post numbered: Posts: 72176 from Administrator. This could be useful and helpful for some projects.

Thanks for all,

BittenApple

[p45cal]

Can you please elaborate what you have done, step by step?

That would take a lot of my time.
See this picture to see what you can do to explore what I've done and do an internet search for the likes of Power Query Tutorial. There are lots.

2021-10-15_165456.png