Create a slicer linking 2 pivot tables from different worksheet

[Liphor]

I am trying to create a slicer linking 2 pivot tables from different sources as shown in the file attach. However I keep getting message that my column have duplicate values. Basically I am trying to link my  Q data and RF data for each month. My slicer should be showing the catch which are of 3 types.

Where did I go wrong here.

[#2]

@Liphor This requires a number of queries:

1. a query for each data table to unpivot the source data
2. a query on each table which extracts the unique Catches
3. a query that appends the two previous queries and keeps the unique Catches (this query will be used to filter both pivot tables)

Make sure all queries are part of the data model.

Now create a pivot table using the data from either of the two queries in (1). Set the page filter of that pivot to the Catches field of query (3). Let Excel detect the relationships. Repeat this step for the other (1) query.

Now insert a slicer for the pivot filter field. Set the report connection of that slicer to both pivot tables.

The end result is the attached.

[#3]

Thank You for your suggestion. I studying your proposal and will get back to you as I am still quite a novice in using excel.

[#4]

@Jan Karel Pieterse  I try to follow your instructions but I am not able to link the splicer to one of the pivot even though I have managed to connect both tables. Attached is my file.

[#5]

You seem to have uploaded an empty workbook?

[#6]

@Jan Karel PieterseOops sorry .Here is the attached file.

[#7 · Accepted solution]

@Liphor There were a couple of issues in your file:

1. The Append query contained two columns because you renamed the Catch column in one of its sources
2. The append query then needed a filter for unique values
3. The pivot tables need to be using the fields from the unpivoted table queries, not from the original tables
4. The Catch field for each pivot needs to be taken from the Appended Catch query, NOT from the table or the unpivoted table query
5. The slicer then needs to be on that Catch field and tied to both pivot tables.

See attached.

[#8]

@Jan Karel PieterseI am finally able to follow the instructions given by you.  I would to know whether do I always have to create a unique query  to different tables and append them together. How do I sort the months in calendar type order. Thank you very much and really appreciate your help.

[#9]

Filter fields in a pivot table from the data model need to come from a table which contains only unique items of all tables you are filtering on. I'm no data model expert, I'm sure if you google for the month sorting problem you'll find the answer, which I expect will revolve around creating a date table using a query.

[#10]

Thank you again for your help