Combining sheets in Excel using PowerQuery in less than 50 clicks (no VBA or coding required)

A recent discussion on R/Excel "What is the most efficient way to insert a similar chart in several pre-existing worksheets of a workbook while making sure that the data source of the chart refers to the worksheet where it has been inserted?" initiated a conversation about the different methods of displaying data from multiple sheets which included a question around working with data on multiple sheets that needed to be summarised on a chart that was identical on each sheet.

As with most things with Excel, there are a number of methods that would achieve this result, I believe the quickest and most efficient is to use Power Query to combine the data and then display the output as a pivot table/chart with a slicer to allow the user to select each source as required. I realise this isn't providing a separate sheet for each data source however, assuming there are no restrictions on the visibility of the data, this is a 1-page solution that contains all the required data for the user.

The process consists of the following main steps

1. Connecting Power Query to the file using the 'get data' option and then 'folder'

2. Removing unnecessary columns and expanding the data from each sheet

3. Removing unnecessary rows and amending headers

4. Loading data to the data model/sheet

5. Create pivot table, chart and slicers

In this example, I'm using a file I've created titled '16Tables.xlsx' (available here) which contains 16 sheets with data on each of the same format. There are 4 columns, ID, Operator (A-D), Result (a number between 1-100) and outcome (if result >50, "Good" otherwise "Bad"). I've converted the data on each page into a table. This makes the process easier and is a good practice that I advise all my clients.

Here is the file loaded, note the tabs along the bottom and the table on the sheet.

1. In the ribbon select 'Data', 'From File, 'From Folder'

Why aren't we using 'From Workbook' I hear you ask. The method 'From Folder' uses a slightly different method of connecting to the data that is more useful in the method we're using. It is possible to use the 'From Workbook' option but there are a few extra steps required that make it more challenging and less straightforward to those who haven't used Power Query before.

2. In the next window, paste or navigate to the location of the folder containing the file of interested (in this example 16Tables.xlsx) and click 'OK'

After a second or two, the Power Query editor will load and the contents of the folder will be displayed on your screen.

3. Filter the 'Name' column so that it selects only the file you're working with (in this example I'm using 16Tables.xlsx)

You should now see only 1 row listed

4. Select the 'Content' and the 'Name' columns, right-click on one and select 'Remove other columns'. This is purely aesthetic but is strongly recommended

5. Click on the word 'Binary' in the 'Content' column

This will create two steps in the 'Applied Steps' section of the 'Query Settings' pane, they'll be titled 'Content' and 'Imported Excel' and be displayed like this

Additionally, the data in the preview window will now list all the elements in the spreadsheet

You might notice that for each table there are 2 items, a sheet and a table, we need to filter this list by completing the following step

6. Filter the 'Kind' column to only show 'Sheet'

7. Select the 'Name' and 'Data' Columns, right click on one and select 'Remove Other columns' again this is aesthetic but highly recommended

8. Click the 'Expand' button next to the title of the 'Data' Column, ensure all the column titles are ticked and click 'OK'

Now all the data from each table will be displayed in a single table. Note the change of Table Name after row20

Note - the row titles are visible at the beginning of each table and the column titles are incorrect, this can easily be changed by the following steps

9. Click the small table menu button and select 'Use First Rows as Headers'

10. Rename the first columns 'SheetName' by double clicking on it, typing in the preferred column name and hitting enter

11. Filter the ID column to remove the rows containing 'ID' and click ok. This will remove the column headers from the 21st row and all subsequent rows where it is listed. This step could be applied to any row where the column header is easily identifiable

Now your table is complete

12. To use the table click 'Close and Load' in the ribbon and select 'Close & Load'

After a second or two the combined table will be loaded into a new sheet in excel and the query listed on the right-hand side of the window

13. To create a pivot table from this new combined table, click 'insert', 'pivot table' from the ribbon, ensure 'Query1' is listed as the table/range then click 'ok'

14. Create the desired pivot table, in this example, I've dragged 'Outcome' to columns, 'Operator' to Rows and 'Outcome' to Values which defaults to the count of outcome

The displayed pivot table now shows the number of good/bad results for each operator but across all sheets. To make this page more interactive we'll add a slicer to toggle the sheets so the user can isolate each sheet or select multiple

15. In the ribbon under 'Pivot Table Tools', 'Analyse' select 'Insert Slicer' then 

16. Then tick 'SheetName' and click 'ok

17. Drag and resize the slicer element once it is added to the page

18. Add in a clustered column chart from the ribbon (Insert, charts, stacked column) and then rearrange the elements so none are overlapping and that’s it,

Clicking on the values in the slicer will toggle the data that is filtered in the pivot table as shown in the following image'

To update the data in the future, all that is required is a full refresh using the 'Data' then 'Refresh all' option in the ribbon.

Note - if your data is configured as tables, the column headers will automatically load and not need manual amending. Also, Power Query will remove the headers from any subsequent tables.

The M Language created in Excel to support this query is as follows

let

    Source = Folder.Files("C:\Users\...),

    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "16Tables.xlsx")),

    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),

    Content = #"Removed Other Columns"{0}[Content],

    #"Imported Excel" = Excel.Workbook(Content),

    #"Filtered Rows1" = Table.SelectRows(#"Imported Excel", each ([Kind] = "Sheet")),

    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows1",{"Name", "Data"}),

    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Data", {"Column1", "Column2", "Column3", "Column4"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4"}),

    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data"),

    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Sheet-1", "SheetName"}}),

    #"Filtered Rows2" = Table.SelectRows(#"Renamed Columns", each ([ID] <> "ID"))

in

    #"Filtered Rows2"