NEW! FREE 30 Minutes Excel Conditional Formatting for Beginners! Watch now 

Power BITutorials

Consolidate Multiple Excel Sheets Using Power Query

1 Mins read

If you have multiple Excel worksheets that are in the same format and their underlying differences are their values and dates (e.g. January Sales List, February Sales List, March Sales-List, etc), then we can easily consolidate all the worksheets into one. The advanced excel function like Power Query will help you to consolidate multiple excel sheets into one.

 

Consolidate Multiple Excel Sheets By Power Query

Let us understand this step by step.

Step 1: Load Data in Power Query

First, load each of the worksheet data individually in Power Query Editor that you want to consolidate as shown below.

LOAD DATA IN POWER QUERY

Step 2: Close & Apply

Just close and apply each table after loading into Power Query Editor to apply the changes.

File  >  Close & Apply

CLOSE & APPLY

Step 3: Append Queries

To consolidate all the worksheets into one, Power Query has an amazing feature called Append Queries. This feature can combine all your data tables into one sheet in a vertical sequence. To use this feature, go to-

Home  >  Append Queries

 APPEND QUERIES

Step 4: Add Tables to Append

After you click on the Append Queries option, the Append Queries window gets open where you can add tables to append. Click on the option ‘Three or more tables’ so that you can add as many tables you want to. Then select the sheets one by one from the list and click on the Add button. Sheets added are displayed on the right side in the window. After you are done, click on OK.

 

ADD TABLES TO APPEND

Step 5: Scroll down to check the Appended tables

Now, you will find all your data into one single table as shown in below picture.

APPENDED TABLES

You can learn more :

Related posts
Power BITutorials

Opportunity Analysis Power BI Dashboard

1 Mins read
Information: A software company that has two sales channels: direct and partner. The sales manager created this dashboard to track opportunities and…
Power BITutorials

Procurement Analysis Power BI Dashboard

1 Mins read
Information: It analyzes a manufacturing company’s spending on vendors by category and location. we explore these areas: Who the top vendors are…
Power BITutorials

Retail Analysis Power BI Dashboard

1 Mins read
Information: It analyzes retail sales data of items sold across multiple stores and districts The metrics compare this year’s performance to last…