Excel Data Transformation: Combine 4 Sheets in Excel using Power Query - Episode 2178

6 лет назад

72,185 Просмотров

Microsoft Excel Tutorial: How to combine four Excel sheets where each has a different number of rows?

Welcome to another episode of the MrExcel Podcast, where we help you learn Excel in a fun and easy way. In today's episode, we will be discussing how to merge four worksheets in Excel using Power Query. This question comes from Doug, who is struggling with using formulas to combine his sales data from four different regions. As the number of records changes monthly, it becomes a nightmare to manage. But fear not, Doug, we have a solution for you using Power Query.

First, let's make sure you have the necessary tools. If you have Excel 2010 or 2013, you can download Power Query for free from Microsoft. If you have Excel 2016 or Office 365, Power Query is already built-in. Now, let's take a look at the four reports that Doug has - Central Region, East Region, South Region, and West Region. Each report has a different number of records, which will change every time the report is run. We have also made some assumptions, such as there not being a column called Central and purposely messing up the data in the South Region to show the power of Power Query.

To start, we will format each worksheet as a table using the shortcut Ctrl+T. This will make it easier for Power Query to work with the data. We will also rename each table to match its respective region. Now, instead of using the traditional Close & Load option, we will choose Close & Load To... and select Only Create Connection. This will create a connection to the data without actually loading it into the worksheet. We will repeat this process for all four tables.

Next, we will insert a new blank worksheet and use the Get Data option to Combine Queries. We will choose to append two queries from this workbook and select Three or more tables. We will then add the four tables and click OK. This will combine all the data from the four tables into one superset. We can then Close & Load this data to a new worksheet. And the best part? If we add more data to any of the four tables, we can simply click Refresh in the query and the new data will be added.

Power Query is an amazing feature from Microsoft that makes data manipulation and analysis so much easier. It may take a little longer to set up initially, but the time saved in the long run is well worth it. We want to thank Doug for sending in this question and we hope this solution helps you and others facing a similar issue. And don't forget to check out the book "M is for (DATA) MONKEY" by Ken Puls and Miguel Escobar for more in-depth knowledge on Power Query. Thanks for tuning in and we'll see you next time for another episode of the MrExcel Podcast.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

#excel
#microsoft
#microsoftexcel
#exceltutorial
#walkthrough
#evergreen
#excelconsolidate
#powerquery

This video answers these common search terms:
how to combine tables power query excel
how to combine sheets in excel by power query
how to merge two tables in excel using power query
can you merge a table with a query excel powerquery
how to merge sheets into one sheet in excel
how to merge sheets in excel into one sheet
how to merge sheets excel
how to merge to sheets in excel
how to merge 2 excel sheets to one
how to merge 3 excel sheets into one
how to merge several sheets into one in excel
how to quickly merge sheets in excel
how to merge all excel sheets into one sheet
how to merge two excel sheets into one sheet
how to merge different excel sheets into one
how do i merge 2 excel spreadsheets
how do i merge two excel spreadsheets into one sheet
how combine sheets in excel
how to combine sheets in excel by power query
how to combine sheets into one sheet in excel
how to combine sheets from multiple excel
how to combine multiple sheets in one excel into one sheet
how to combine several excel sheets into 1 sheet
how to combine data in different excel sheets

Table of Contents:
(00:00) Combine Four Excel Sheets
(00:10) The Problem with Formulas
(00:21) Using Power Query
(01:31) Setting Up the Tables
(03:53) Combining the Tables
(05:46) Refreshing the Data
(06:44) Clicking Like really helps the algorithm

Use Power Query
Format each worksheet as a Table with Ctrl+T
Rename the tables
For each table, new query From Table. Add a custom column for Region
Instead of Close & Load, choose Close & Load to… Only Create a Connection
Use New Query, Combine Query, Append. 3 or More Tables. Choose the Tables and Add
Close and Load and the data appears on a new worksheet
For the one table with extra column: the data shows up for only that sheet's records
For the one table where the columns were in the wrong order: Power Query worked correctly!
Easy to Refresh later

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1154003/

Тэги: