Skip to main content

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"



Labels:

Comments

Post a Comment

Popular posts from this blog

How to combine multiple files with Power Query (with no VBA and just 10 mouse clicks!)

The need to combine information from multiple files is one that most users of Excel will have come across at some point in the use of Excel. I've personally spent far too many hours aggregating data from multiple files, that are identical in structure, so that I can analyse larger datasets and provide insights into products and processes. For anyone who has also done this and not yet discovered Power Query you'll probably be amazed how simple the process has become. I realise there might be some who will say "just use VBA, its easy once you learn how to code..." and they would be right. The method using Power Query provides a zero code solution that is an evolution of the Excel interface that many will already be familiar with. In this example, I've created a sample file and created a number of duplicates of the file which I've saved in a folder. The folder contains only these files and i'd recommend you do the same if you're looking to try out this pr...
Post a Comment
Read more

Extracting data from Word (.docx) files into Power Query

Word and Excel don’t usually get along too well so it's no surprise that Power Query isn't directly compatible with its estranged cousin Word either. If you are presented with the need to import data from Word into Power Query you'll be please to hear it is possible however it requires a couple of manual steps to make it work.  The manual steps could fairly easily be completed by a batch file which would automate the process further. Here is the Excel data pasted 'as values' in a Word file which i'll use for the first example Here is the Excel data pasted with 'keep source formatting' which i'll reference a couple of times in the article. Although the steps I've covered below aren't complex, this whole process has some unknowns around it so you may find the result in your instance varies from mine. The Word file I've used contains the contents of a range of excel cells that I deliberately pasted as values into Word to create a test file f...
Post a Comment
Read more

How to automate the import of all files in a Google Drive folder to PowerBI, now updated, please read the first paragraphs!! (PowerQuery)

Update!!! This method no longer works although there is a new Google Sheets connector for PowerQuery that is currently in beta. If you're using PowerBI, you'll need to enable the preview features to enable it. https://docs.microsoft.com/en-us/power-query/connectors/googlesheets In the interest if demonstrating how it 'did' work, the original post is provided below. If you attempt to replicate this, you'll quickly realise the website doesn't behave as it used to. (here is the original article)  Using PowerQuery to access multiple files within the same folder on a local or network drive is a game-changing feature that will almost certainly save many people hours of effort. This functionality is great if your data exists in a place that is easily accessible but what do you do if your data is somewhere less accessible like Sharepoint, OneDrive or even Google Docs? I have previously connected to data on SharePoint and found it fairly straight forward which raises the...
5 comments
Read more