One slicer for two or more Pivot Tables using PowerQuery (Get&Transform)

[sandy666]

Simple example as usual

Two range tables changes to Structured Tables
Basic rule: unique Primary Key for both tables

1. Click on the first table
2. On the ribbon go to DATA - From Table
3. in new window select Close&Load to...
4. then select Only Create Connection

do the same for the second table

1. From Workbook Queries first query (right click) Edit
2. From Query Editor - Home - Merge Queries - select Merge Queries as New...
3. You've first table loaded then select from empty field - second query
4. then click on Primary Key column (PK) on the first selected query and Primary Key column on the second query
5. at the bottom in Join Kind select Left Outer (bla bla bla)
6. press OK (If OK button is inactive refresh second query then OK)
7. In Query Editor you'll see new table called Merge1 (you can change this name to more friendly if you wish. I changed it to : Common)
8. In this new table last column (or more) has header: NewColumn
9. click on double arrow and from menu choose appropriate field from the second query
10. go to Home - Close&Load to... - Only Create Connection
11. Close Workbook Queries

• Now create Pivot Table(s) (Insert - Pivot Table)
• from pop-up select: Use an external data source
• click on Choose connection
• select Query - Common (remeber I changed Merge1 to Common)
• Untick Add this data to the Data Model if you wish
• select New worksheet or choose place in existing sheet
• OK

• Click on the first Pivot Table and from the tab PivotTable Tools, Filter, select Insert Slicer (what kind of slicer it's up to you)
• then right click on slicer and select Repoert Connections...
• and select these PTs which you want to manage by this slicer

on the end click Add Rep if you like it (bottom left corner)