Combine Multiple Worksheets into one using Power Query [Consolidate Data in Excel]

Today, I'm gonna show you how to combine multiple Worksheets into one using Power Query. Each tab contains more than 100,000 records.
Click the Data tab. Choose 'Get Data From Excel Workbook'.Browse and import the Excel file. In the Navigator pane, you can see the Workbook and all Worksheets. Click the folder icon. Then click the Transform Data button. The Power Query window will appear. We only need to keep the Data column that contains tables. Click the column header to select the entire column. Locate the Manage Column Group. Use the 'Remove other columns' command. Click the expand icon. Make sure that the name prefix box is unchecked. Click OK to get back to the Power Query window. We are consolidating multiple sheets, so there are multiple headers. It is worth to hide these rows to keep our data clean. Select Column 4 and uncheck the records that contain the Items keyword. In the first column, we have dates, so it is worth changing the default number format to date. Change the column names. Using this step, our output will be easy to read. Click the close and load button. Power Query will load and merge all Worksheets into a single Worksheet.

Chapters:

0:00 Intro
0:11 Consolidate Multiple Worksheets into one using Power Query
0:18 Import Data
0:35 The Power Query window
0:38 Remove unnecessary columns
1:06 Filter duplicated headers
1:26 Merge Worksheets into one


#msexcel #excel #exceltips #exceltricks #excelformula #productivity #datacleaning #datacleansing #powerquery #powerquerytutorial

свернуть