Welcome! Enter below articles or choose one from the menu above

HLOOKUP

In this lesson you will learn HLOOKUP function.

Read more ...

How to open .VCF files?

Below are the steps to open a VCF file in Excel.

Read more ...

File recovery

In this lesson you can learn how not to loose your workbook because of breakdown.

Have you ever wondered what happens if you have not saved file, and Excel would suddenly be turned off?

Read more ...

How to calculate ratio?

Ratio: In simple mathematics, relationship or comparison between two more numbers is known as ratios. Ratios are normally written as ":" to show the connection between two numbers, for instance. If there are 2 red balls and 3 blue ball on the table you could write there ratio as: [2:3]

Read more ...

How to Get Data from another Sheet?

There are times when grabbing data from another sheet is necessary. This is why we will get data from another sheet.

Read more ...

In this Excel lesson you will teach yourself how consolidate tables with Power Query. This is wonderful way to boost your efficiency and save bunch of time.

I have a worksheet. There are a few tables in there and I'd like to combine them into one table. Power Query will help me with that.

To join tables click Power Query in the ribbon menu. Choose From File - From Excel.

power query from file from excel

Choose the file in your local drive.

power query from file

Power Query Navigator window pops out.

power query navigator

Select the whole file and click Edit.

power query editor

I can see every objects in the whole workbook. I'd like to load only tables. So I need to click Kind and filter only tables.

power query kind filter

Next I'd like to remove obsolete columns. I don't need so many of them.

power query remove columns

To merge tables I need to press a small icon next to Data header.

power query merge tables

I unchecked Use original column name as prefix option. Just because I don't like it.

This is how preview of my consolidated tables looks like.

power query merged tables

Here is the time for final formatting.

First thing is to change the name. I did it on the right side of the screen.

Next important thing is to set proper columns data formatting. Click the small icon in the top headers and set right data type for each column.

When you are happy from your merged tables, and I am happy, click Close & Load To in the left top side of the screen.

power query close and load to

I'd like to have Table in the New worksheet. Click Load to get merged tables in the new sheet.

power query load to

And here is how my joined tables by Power Query look like.

power query tables merged

I saved a bunch of time because now I have joined tables which don't require any more formatting.