To connect to the Excel file with the multiple sheets of data, the first thing to do is save down the dataset that you wish to connect to and then close the file. Closing the file is very important as PowerQuery cannot connect to an open file.

Once the file is closed, open a new Excel workbook. In the new workbook, select the Data tab and then on the Get Data and Transform section of the ribbon, select the Get Data dropdown.

From here, select the From File option and then the From Workbook button. Excel will then ask you to select the file that you wish to connect to.

Once you have selected your file, the Navigator menu pops up asking you to select the sheet in the file that you wish to connect to. To select to multiple tabs you can select the ‘Select Multiple Items’ checkbox and then select the sheets that you’d like to connect to. However if you have to perform this task frequently, and the dataset sometimes has different sheet titles, then a better option would be to right click on the Folder title (in the video this is called ‘Dataset’) and then select Transform Data. This way, all the sheets will be connected to and in the PowerQuery Editor screen you can choose which sheets to exclude specifically, but all other sheets will be connected to.

Once we’re in the PowerQuery Editor, you will see a table showing the names of each of the sheets we can connect to. You can filter these if you wish to. Once you have the sheets you wish to connect to then in the top right hand corner of the Data Column Header, there is a button with two arrows heading in opposite directions. Click on this to expand all the data tables. You can

From here, you’re then able to perform any transformations that you wish, but there are then at least three transformations which you would generally perform.

a) Delete unnecessary columns. These are generally the first column and then the last three columns (called ‘Name’, ‘Item’, ‘Kind’ and ‘Hidden’). These are columns of data that have not come from the Sheets but the first view when you enter PowerQuery.

b) Promote Headers. Using this technique, you can’t tell PowerQuery that the data you’re connecting to has Headers at the initial connection so you have to do it in the PowerQuery Editor by selecting ‘Use First Row as Headers’.

c) Remove Headers from all tabs. Even though you’ve already Promoted Headers, this only promotes the first row in the dataset. The Headers from all the other tabs will still be in the dataset somewhere so we need to remove these. Simply go to any column header and select the drop down arrow. You’ll then see all of the expected rows based on the fields of that column but you’ll also see the column title in the list. Deselect it.

Once all transformations have been performed, on the Home tab, select Close and Load To. This will load the data back into Excel, but will give you the option to load into a Pivot Table or a Pivot Table Chart, rather than a standard table.