Combining 2 pivot tables into 1 chart

[Ramballah]

Hello everyone,

My question sounds simple but I do not know the answer.
I have 2 pivot tables and 2 charts that go with this. However I want to merge them into only 1 chart.
I have no intention of combining the raw data together because we are resetting the data from both tables weekly.
So what we want is the 2 tables and 1 chart consisting of the 2 tables. Here is my example (sorry its dutch)
Lets call the table on the left table A and the table on the right table B.
The top chart belongs to table A and the bottom chart belongs to table B.
I want to see both table A and B but only have one chart. The design for the chart im not entirely sure what looks best.
Currently im thinking about having table A go upwards in the chart and table B downwards. Or have both tables next to each other so I can see which table is higher.
I hope I didnt confuse anyone and that someone can help me with this.
Thanks in advance,
Ram

 

[sandy666]

in a meantime look at this

more about PowerQuery aka Get&Transform

• What is PowerQuery (Get&Transform)
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference (erratum required)

to be continued...

 

[sandy666]

- use Ctrl+T for both ranges: debtor and creditor
- then use Data tab - From Table for each just created tables
it will open Power Query Editor
- remove all not necessary columns in both tables
- group each table by week and year and sum values
- sort both ascending (for nice look )
- merge table1 and table2 with Full Outer option
- Close&Load to...
- choose as Connection only
---------------------------------
Insert tab - Pivot Chart - Pivot Chart & Pivot Table
- use an external data source
- choose Query - Merge1 - Open
- choose place where your pivot should be
- set fields in areas
- play with chart to make it what you want

I hope I haven't missed anything

 

[GraH]

I was busy and Sandy666 made it work just fine, but since I did put in the effort wanted to share my solution as well.

easyupload.io

easyupload.io

easyupload.io

(My native language is not English I am sorry)

Just saying you are allowed to post in your own language here and Dutch could have worked .

 

[Ramballah]

- use Ctrl+T for both ranges: debtor and creditor
- then use Data tab - From Table for each just created tables
it will open Power Query Editor
- remove all not necessary columns in both tables
- group each table by week and year and sum values
- sort both ascending (for nice look )
- merge table1 and table2 with Full Outer option
- Close&Load to...
- choose as Connection only
---------------------------------
Insert tab - Pivot Chart - Pivot Chart & Pivot Table
- use an external data source
- choose Query - Merge1 - Open
- choose place where your pivot should be
- set fields in areas
- play with chart to make it what you want

I hope I haven't missed anything

Hi,
Sorry for the late response, I tried doing what you said today however it never let me merge the queries. I can select both tables and full outer option but then I cannot proceed any further.
Do you have any idea what this could be?

 

[Ramballah]

I was busy and Sandy666 made it work just fine, but since I did put in the effort wanted to share my solution as well.

easyupload.io

easyupload.io

easyupload.io

Just saying you are allowed to post in your own language here and Dutch could have worked .

Hi,
Thanks for your solution. I tried it but changed the references since they are in different workbooks.I get the #name error. Could you tell me if I did something wrong?

Edit*: I noticed I referenced it wrong. Let me try again.

 

[Ramballah]

I was busy and Sandy666 made it work just fine, but since I did put in the effort wanted to share my solution as well.

easyupload.io

easyupload.io

easyupload.io

Just saying you are allowed to post in your own language here and Dutch could have worked .

I moved some stuff to my own file to test it but I'm still getting this error.
Just like your file in sheet "Debiteuren" is a pivot table with column A being the week numbers.
Crediteuren has the same
EDIT*:
I just looked on the internet and saw that this means that we are using different versions. To which I should mention that since I am at work, we use Excel 2019 apparently. At home I use 365 but I don't need this file at home... I am sorry

 

[GraH]

I went with your profile version indeed. I don't have Excel available for the moment. I might revisit this later, but can't promise.

 

[Ramballah]

I went with your profile version indeed. I don't have Excel available for the moment. I might revisit this later, but can't promise.

Well perhaps someone else know a workaround with the function Sequence

 

[GraH]

Well perhaps someone else know a workaround with the function Sequence

Like this

easyupload.io

easyupload.io

easyupload.io

 

[sandy666]

I can select both tables and full outer option but then I cannot proceed any further.
Do you have any idea what this could be?