Linking two pivot tables
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Linking two pivot tables
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
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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Linking two pivot tables
Does this help? Pivot Table Multiple Consolidation Ranges
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 151
- Joined: 11 Jun 2012, 20:37
Re: Linking two pivot tables
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.
Best attach a workbook with the source data tables and some idea of the pivot tables you want.
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Linking two pivot tables
Thanks fir the response. Are data model or power quer two different things?
Respectfully,
BittenApple
Respectfully,
BittenApple
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 151
- Joined: 11 Jun 2012, 20:37
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Linking two pivot tables
Hello team,
Since you requested data, I have attached a file. I will go ahead and read the link, Thanks for it.
BittenApple
Since you requested data, I have attached a file. I will go ahead and read the link, Thanks for it.
BittenApple
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Linking two pivot tables
BittenApple wrote: ↑12 Oct 2021, 03:34Hello 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
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Linking two pivot tables
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
Is it like to say data model is as joining tables by linking through common fields and power query is as query?
Thanks,
BittenApple
-
- 2StarLounger
- Posts: 151
- Joined: 11 Jun 2012, 20:37
Re: Linking two pivot tables
I think so, although a query can just append the tables.BittenApple wrote: ↑12 Oct 2021, 03:39Is it like to say data model is as joining tables by linking through common fields and power query is as query?
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.
You do not have the required permissions to view the files attached to this post.
Last edited by p45cal on 12 Oct 2021, 19:05, edited 1 time in total.
-
- 2StarLounger
- Posts: 151
- Joined: 11 Jun 2012, 20:37
Re: Linking two pivot tables
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.
You do not have the required permissions to view the files attached to this post.
Last edited by p45cal on 12 Oct 2021, 19:05, edited 1 time in total.
-
- 2StarLounger
- Posts: 151
- Joined: 11 Jun 2012, 20:37
Re: Linking two pivot tables
…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.
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: Linking two pivot tables
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
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
-
- 2StarLounger
- Posts: 151
- Joined: 11 Jun 2012, 20:37
Re: Linking two pivot tables
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.
You do not have the required permissions to view the files attached to this post.