How to combine data ranges with Microsoft Power Query in Excel


Data comes from all kinds of sources and Microsoft Power Query can you when you need to combine foreign data. It’s a bit easier than trying to do so in Microsoft Excel sometimes.

A blank Excel spreadsheet
Image: PixieMe/Shutterstock

Microsoft Power Query showed up in Excel 2016, though earlier versions can access Power Query using an add-in. This tool allows you to import, scrub and edit data from foreign sources and then use it in Excel or prepare it for Power BI. It’s a great tool to have in your tool chest. In this article, I’ll show you how to combine three Excel data ranges in Power Query. We’ll also add and populate a new column to help with later filtering and analysis.

SEE: Software Installation Policy (TechRepublic Premium)

I’m using Microsoft 365 on a Windows 10 64-bit system. Power Query is available as part of the interface through Excel 2016. Excel for the web doesn’t support Power Query, but you can run existing queries in an Excel workbook. You can download a demonstration file.

The Excel data

Many users store related types of data in separate sheets, divided by some kind of entity that makes sense to the data. For instance, you might keep monthly sales sheets or monthly credits and debits sheets. Or you might track sales and commissions by personnel sheets—one sheet for each person.

This type of setup works fine until someone wants to see the bigger picture—a review of all the monthly sales figures along with the details or all the sales and commissions for all the employees. If you’re using 3D referencing, you might have a sheet somewhere that’s combining all the data, but that sheet won’t display the details, only the subtotals and totals. In addition, this arrangement requires that the sheets have the same structure. That won’t always be the case.

Figure A shows a simple sales and commission sheet by personnel for the month of January. There are two other sheets for February and March. This is where Power Query comes in. We’ll use it to quickly combine the January, February and March sales and commissions.

Figure A

There are three sheets of sales and commissions for the year so far.
There are three sheets of sales and commissions for the year so far.

How to load the Excel data

The first step is to load the three data sets into Power Query. If you’re not working with Table objects, Power Query will prompt you to convert the data range to a Table. Power Query works only with Excel Table objects. In fact, the table names are January, February and March, accordingly. You’ll see these names later in Power Query.

Let’s start by loading January’s data:

  1. Click anywhere inside the Table in the January sheet.
  2. Click the Data tab.
  3. In the Get & Transform data group, click From Table/Range.

That’s all there is to it. Figure B shows the data in Power Query.

Figure B

Power Query loads the data from the selected source.
Power Query loads the data from the selected source.

At this point, you might want to consider adding the month to the Power Query table. In Excel, the month is identified by each tab. However, that tab name doesn’t go along with the data. If the person asking for the combined data wants any analysis by month, you’ll need to add it. So, let’s do that next:

  1. In Power Query, click Add Column.
  2. In the General column, click Custom Column.
  3. In the resulting dialog, enter a meaningful name. We’ll use this name again when we load the other two data ranges into Power Query. In the Custom Column Formula, enter “January” (Figure C). You must enter the double quotation marks (” “) to denote the entry as a string.

Figure C

Create the new column for the month of January.
Create the new column for the month of January.
  1. Click OK to see the new column (Figure D).

Figure D

Power Query adds the new column.
Power Query adds the new column.

We’ll add the month to each data set that we add. When adding the Month field to each new table be careful to enter the exact same name—this feature is case-sensitive. If you enter “MOnth,” the second time around, you will end up with two new columns, not one.

Exit Power Query and save your new table when prompted. You must do this between loading data for each month. Power Query will load the Power Query table into a new sheet. Don’t worry about this for now.

Now, repeat the first set of instructions above to load the data in the February sheet. Then, repeat the second set of instructions to add the Month column and populate it with February. Repeat this all again, with the data in the March sheet, add the Month column and populate it with March.

With all three tables in Power Query, you’re ready to append them into one.

How to append the tables in Excel

Remember when I mentioned that the Excel Table names were January, February and March? The sheet names are also January, February and March.

At this point, all three months are in Power Query as tables and each table now has an extra column: The Month field identifies each month. Those new Power Query tables are also in Excel and named accordingly: January (2), February (2), and March (2). Now we’re ready to append the three Power Query tables in Excel—you don’t have to go back to Power Query.

To append the three tables, click inside the Power Query table (January 2) and do the following:

  1. In the Queries list to the right, right-click the January query—at this point, Power Query calls them queries.
  2. In the resulting list, click Append (Figure E).

Figure E

Choose Append.
Choose Append.
  1. In the Append dialog, click the Three Or More Tables option.
  2. Click the second table, February, in the Available Tables list—the first table, January, is already a part of the query—and click Add.
  3. Click the third and last table in the Available Tables list and click Add (Figure F).

Figure F

Append the three tables into one.
Append the three tables into one.
  1. With all three tables in the Tables to Append list, click OK.

As you can see in Figure F (at least partially), all three data sets, January, February and March, are now part of the same table (query) and each record identifies it by the month. In the left pane, the name of this query is Append 1. At this point, you can click Close & Load in the Close group to save the new table to Excel. Power Query will name the new sheet Append 1, which you can change. But now you have all three months of records to sort, filter and analyze in anyway you like, and it only took a few minutes to combine all that data. Our Excel Tables were simple, but you can see the advantage when dealing with lots of data.



Source link