Tags

,

Microsoft released an extension for Excel called Power Query. It makes some tasks easier, like combining Excel worksheets in a single one. This is the subject I am going to talk about, but in the future I will cover more about Power Query.

Let’s assume we have a worksheet containing foods.

Food Worksheet

Food Worksheet


And we also have another worksheet containing electronics.
Electronics Worksheet

Electronics Worksheet


Both worksheets have the same columns and we want to combine them into a single worksheet to display all data. With Power Query this becomes an easy task. You just need to follow these steps.

Creating connection from tables

Click inside a worksheet used range and from Power Query ribbon choose to get data from table.

Get data from table

Get data from table


You are asked to choose the range you want to use for data. By default used range is selected.
Choose range

Choose range


Query editor window is opened. Just give a name to your query (I named it “Table_Food”) and choose “Close & Load To” option from “Close & Load” menu. From the new window choose “Only Create Connection” as we already have data into table.

Load To

Load To

Repeat this operation for the other worksheet, of course giving a different name for the connection (like “Table_Electronics”).

Create the connection to combine the data

To create a connection to show combined data, choose “Show Pane” from Power Query ribbon.

Show Pane

Show Pane


On the “Workbook Queries” pane, right click on any data connection and choose “Append”.
Choose Append Option

Choose Append Option


On the next screen choose source and destination for append query.
Choose source and destination

Choose source and destination


Give a name for your new query (like “Combined Data”) and again choose “Close & Load To” option from “Close & Load” menu, of course only creating connection.

Display data

Now, as you have created all connections, go to “Data” ribbon and choose to get external data from existing connection.

Get data from existing connections

Get data from existing connections


Choose your connection which displays data from both worksheets and click “Open” button.
Choose connection

Choose connection


On the next screen is really up to you what you choose. You can choose to display data into an existing worksheet or you can tell Excel to create a new for you to place the data. You can consider now the task is done. 🙂

There is an interesting part with Power Query. If, for example, you add new data into one of the worksheets you used as a source and refresh data connections, Power Query knows and put new data into combined connection.

Advertisements