How to combine data ranges with Microsoft Power Query in Excel
Your email has been sent
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.
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.
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
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:
That’s all there is to it. Figure B shows the data in Power Query.
Figure B
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:
Figure C
Figure D
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.
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:
Figure E
Figure F
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.
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
How to combine data ranges with Microsoft Power Query in Excel
Your email has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
This is a comprehensive list of the best AI art generators. Explore the advanced technology that transforms imagination into stunning artworks.
Find the perfect payroll service for your business without breaking the bank. Discover the top cheap payroll services, features, pricing and pros and cons.
Is NordVPN worth it? How much does it cost and is it safe to use? Read our NordVPN review to learn about pricing, features, security, and more.
Free project management software provides flexibility for managing projects without paying a cent. Check out our list of the top free project management tools.
Australian and New Zealand enterprises in the public cloud are facing pressure to optimize cloud strategies due to a growth in usage and expected future demand, including for artificial intelligence use cases.
The end of a year provides an ideal opportunity for revisiting the previous year’s goals and performance, reviewing personal and professional aspirations and prioritizing the upcoming year’s initiatives. But where to start? Tech pros can implement these seven planning steps from TechRepublic Premium to successfully set and track goals and subsequently prioritize initiatives. From the …
The role of a technical copywriter is recognized as a cornerstone in defining and conveying a company’s identity in the ever-evolving landscape of today’s business, where the online presence and narrative of a brand have outstanding value. This hiring kit from TechRepublic Premium provides an adjustable framework your business can use to find the right …
With artificial intelligence being more popular than free alcohol at a tech conference, it’s wise to stay informed about all things AI or even implement policies for its correct usage. This TechRepublic Premium pack provides readers with seven downloads for a bargain price. The bundle comprises two glossaries about AI and machine learning; three features …
A successful chief digital officer drives their organization’s digital transformation and creates value while rationalizing business processes and the customer experience. This hiring kit from TechRepublic Premium provides a workable framework you can use to find the best CDO for your organization. From the hiring kit: EDUCATION AND EXPERIENCE Candidates must have a degree in …
Get the web’s best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let’s start with the basics.
* – indicates required fields
Lost your password? Request a new password
Please enter your email adress. You will receive an email message with instructions on how to reset your password.
Check your email for a password reset link. If you didn’t receive an email don’t forgot to check your spam folder, otherwise contact support.
This will help us provide you with customized content.
Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.
Leave a Reply