Transform and Combine all worksheets in an #Excel workbook with #PowerQuery

Power Query is magically powerful, yet it is not well known to majority of Excel users.  I think most Excel users are not even aware of its existence…  So, let’s talk about how Power Query could solve a real-life problem commonly found at workplace. 😉

One day, I got an IM message from a colleague who attended a few of my in-house Excel Training courses… (I didn’t hold any Power Query session yet; so she has no idea of Power Query at all.)  She asked:

If there is a quick way to combine (append) 100+ worksheets in a workbook. 

I replied:

“Of course… only if you know how!”  😁 I was bad, I know.

I then asked her to share her workbook with all sensitive information removed.

Here’s the content of the worksheets (all data are fake… of course):

Excel Tips - Transform and Combine all worksheets

What she wanted to extract from the worksheets is the range highlighted above, with the metadata “Employee Name” and “Employee ID” being put together.

Here’s the layout of the expected outcome:

Excel Tips - Transform and Combine all worksheets2

No empty columns; no empty rows… just a simple table!

The real challenge

You may be thinking… that’s easy… it can be done in just a minute.  BUT remember this, there are more than 100 worksheets in the workbook and we need to do that to each worksheet.

Excel Tips - Transform and Combine all worksheets3

Then we have to combine all transformed worksheets into a single table:

Excel Tips - Transform and Combine all worksheets4

AND needless to say, we may have more worksheets (employees) in the workbook that we want to apply the same transformation and consolidation in the future.

How much time we need to do it manually?

I guess it could take a full day, or even more…

No worry!  Power Query comes to rescue!

With Power Query, we can set up the transformation and consolidation in minutes.

And the best part is when we have it set up properly, the same query can be applied to other workbooks with the same structure.  Do you know what it means?  It means a life saver – a simple refresh would give you the result in a flash (seconds) with future updates.

Many steps are involved in solving the problem.  Hence it is best to illustrate with a video!

Here’s the video in English:

 

Here’s the video in Cantonese:

 

You may download the sample files from the links under the video to follow along.

I hope you like this video.  If you do, please give a thumb up, subscribe and share it. 😊

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Power Query and tagged , , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.