Combine multiple source data into a single Excel table using Power Query

 

by Kara Hiltz

Application: Microsoft Excel 2010/2013/2016

Operating System: Microsoft Windows

Download: http://download.elijournals.com/excel/201809/powerquery.zip

Power Query is an essential tool for Excel users who want to work with data that comes from several locations. With Power Query, you can connect with the data sources you need, combine and modify the data, and even share the data with others.

To use Power Query to combine data from several sources, we’ll:

  • Connect with the first data source.
  • Append data from a second source into the first data source—and maintain all rows from both sources.
  • Save our new merged table for later use.

Sometimes your Excel data is not all in one place, especially when you work on a team that spans many locations or works with different software. The Power Query feature is a strong data analysis tool that will allow you to connect with data sources and combine dispersed data, as shown in Figure A.

In the July issue of Inside Microsoft Excel, an article titled “Build a Data Model to bring together widespread data for analysis,” we showed you how to add data from many sources into one Data Model—each source on a different tab. This time, we’ll show you how Power Query can help you consolidate data from those outside sources into one large data table.

IME18901A

A: The data in this Query Editor has been merged from several data sources.

Download: If you’d like to follow along with our example, you can download and extract source files from the Zip file powerquery.zip found at the URL given at the beginning of this article.

Get Power Query for Excel 2013 and earlier

Power Query is built in to Excel 2016, but you can also download the feature as an add-in to use in earlier versions of Excel. Go to https://www.microsoft.com/en-us/download/details.aspx?id=39379&WT.mc_ id=Blog_PBI_Announce_DI to download the Power Query add-in.

Connect with your first data source

The first step in using Power Query is to connect with the data sources you want to use. We’ll use the same example we used in the June article to demonstrate. Our sales data comes from: a different worksheet in the same workbook, a separate workbook, and a text (.TXT) file, as shown in Figure B.

The steps you take to connect with a data source will vary slightly depending on the source type . We’ll use the From File option because our sources are Excel files or .TXT files. You can also connectwith files from databases like Microsoft Access, from the web, or even from Facebook.

Note: In Excel 2016, the Power Query functions are part of the Get & Transform area on the Data tab. If you’re using the Power Query add-in for earlier versions of Excel, you’ll use the Power Query tab that displays in your ribbon when you download and activate the add-in.

IME18901B1 IME18901B2 IME18901B3

B: Power Query will help us combine data from three different locations into one large data table.

To connect with data from a source file:

  • Open a new blank Excel workbook.
  • Switch to the Data tab and, in the Get & Transform area, click the New Query dropdown icon to show your options.
  • Hover your mouse pointer over From File to see a second menu listing different file types.
  • Select the file type with which you want to connect. For our example, we’ll select From Workbook, as shown in Figure C. The Import Data dialog box opens.
  • Navigate to the file that you want to add to your Power Query and click the Import button. The Navigator opens. Note that both worksheets within the workbook display in the Navigator window. Select one of those worksheets to preview the data, as shown in Figure D.
  • Select the Select Multiple Items check box, and then select the check boxes beside both wor[...]
 
Join NowClose
Return to the ExcelSkillsSociety's homepage