Combining multiple identical tables into one Pivot Chart

[42tcrawford]

I have 15 identical Tables, each on a different tab, which contain a list of staff and their educational progress. I do need to keep these tables on separate tabs. Each table has identical columns and the only difference is the raw data and the table name.
My aim is to have one master Pivot Table which draws data from these tables as if they were all grouped together. The Pivot Table created will then be used to display various information for analysis.
All I need to know is how to combine identical tables into one dynamic arrangement. I will not be altering the data in this master table, only the data on the original tables. I will use slicers later to display what I need from this master table.
I have tried Get & Transform Data, Relationships and Data Model but do not know enough about these to achieve what I want.
Thank you in advance.

 

[VBA learner ITG]

Hello,

Here are the steps to combine multiple worksheets with Excel Tables using Power Query:

Go to the Data tab.Combine Data from Multiple sheets - Data tab
In the Get & Transform Data group, click on the ‘Get Data’ option.Combine Data from Multiple sheets - Get Data
Go the ‘From Other Sources’ option.

Click the ‘Blank Query’ option. This will open the Power Query editor.Combine Data from Multiple sheets - Blank Query
In the Query editor, type the following formula in the formula bar: =Excel.CurrentWorkbook().

Note that the Power Query formulas are case sensitive, so you need to use the exact formula as mentioned (else you will get an error). Combine Multiple sheets - Excel CurrentWorkbook formula
Hit the Enter key.

This will show you all the table names in the entire workbook (it will also show you the named ranges and/or connections in case it exists in the workbook).