Excel Power Query (Get & Transform) Tutorial for Beginners: Import, Edit, Load and Consolidate Data with this Step-By-Step Guide

In this Power Query (Get & Transform) Tutorial for beginners, you learn how to use Power Query (Get & Transform) to automate the process of importing and consolidating data in Excel.

The following are the main topics you learn about:

• What Power Query is and how it can help you.
• What data sources can Power Query import data from.
• How to import data from 3 commonly-used data sources (Excel workbooks, CSV and text files) with Power Query, including the following:
  • How to import single or multiple data sources from an Excel workbook.
  • How to import data from CSV or text files.
  • How to edit a query during the process of importing data with Power Query.
  • Where and how to load the data you import with Power Query.
  • How to combine and consolidate the data you import from different files with Power Query.

This Power Query Tutorial is accompanied by several example files containing the data, queries and connections I use in the examples below. You can get immediate free access to these example files by clicking the button below.

This Power Query Tutorial was most recently updated in March 2018 with Excel Version 1802 (Build 9029.2253 Click-to-Run). Microsoft is constantly updating/modifying Power Query. If you're working with a different version of Excel/Power Query, some details (particularly menus) are different. The principles and basic processes you learn in this Power Query Tutorial, are generally applicable.

Related Tutorials

The following Tutorials may help you better understand and implement the contents below:

• Tips and tricks:
  • Learn how to work with keyboard shortcuts here.
  • Learn 350+ Excel keyboard shortcuts here.
  • Learn several ways to convert PDF files to Excel here.
• Formulas and functions:
  • Learn the basics of cleaning data by working with Text functions here.
• Macros and VBA:
  • Learn the basics of working with macros here.
  • Learn about basic VBA constructs here.
  • Learn how to convert PDF files to Excel with VBA here.

You can find additional Tutorials in the Archives.

What Power Query (Get & Transform) is

One of the first steps in the process of analyzing data in Excel is importing it. You can achieve this in several ways. Common approaches involve manually importing data or using VBA to automate the process. These approaches may not be the most appropriate in several cases.

• The process of manually importing data into Excel is generally slow, repetitive, tedious and error-prone.
• Macros and VBA can help you automate a lot of your data importing processes. This approach, however, requires some programming knowledge and, depending on your situation, you may need to spend time maintaining the procedures you use.

Power Query (Get & Transform) allows you to import data into Excel. Power Query offers several advantages (vs. the common approaches I list above), including the following:

• Like VBA, Power Query allows you to automate the process of importing data.
• However, when compared with VBA, Power Query is generally easier to work with and maintain. Power Query also tends to result in better performance (vs. VBA).

Overall, you can think of Power Query as an ETL tool. In other words, Power Query allows you to:

• Extract: Use Power Query to discover and connect to a variety of data sources.
• Transform: Transform the extracted data by, for example, combining or refining it.
• Load: Share the transformed data.

Data sources you can connect to with Power Query (Get & Transform)

You can connect Power Query to different data sources, including the following:

• Files: Excel files, Text or CSV files, XML files, and JSON files.
• Databases: Microsoft SQL Server, Microsoft Access, SQL Server Analysis Services.
• Other example data sources: Microsoft Exchange, Facebook, SalesForce, Excel Tables or named ranges, web pages, and OData feeds.

From a broad perspective, the 2 data source types you're most likely to work with are:

• Relational databases; and
• Files.

This Power Query Tutorial focuses on working with the 3 following common file types:

1. Excel workbooks.
2. CSV (comma-separated value) files.
3. Text files.

As you learn in the following sections, Power Query usually imports the data within a CSV or text file as follows:

• Each line within the file is a row of data.
• Columns are determined by the appropriate delimiter (for example, comma in CSV files or tab in tab-delimited files).

File examples for this Power Query (Get & Transform) Tutorial

You can access the example files that accompany this Power Query Tutorial by clicking the button below.

There are 3 source file examples:

1. Excel workbook.
2. CSV file.
3. Text file.

These source file examples contain several thousand rows with the following (fictional) sales data:

• Date: Between January 1 of 2018 and December 31 of 2019.
• Item: 1 of the following Microsoft devices:
  • Surface Pro.
  • Surface Laptop.
  • Surface Book 2.
  • Surface Studio.
  • Xbox One X.
• Store: I assume there's 1 store per city listed below. I divide the cities in 4 regions (Midwest, Northeast, South and West). The source files contain 20,000 rows of data for each region.
  • Midwest: Chicago, Detroit and Minneapolis.
  • Northeast: Boston, New York and Philadelphia.
  • South: Atlanta, Dallas, Houston, Miami, Tampa and Washington D.C.
  • West: Los Angeles, Phoenix, San Diego, San Francisco and Seattle.
• Units sold: Between 1 and 5.
• Unit price: I assume the price remains constant regardless of the store location and date of sale:
• Total sales: The product of units sold times unit price (UnitsSold x UnitPrice).

The following are the differences between the different source file examples.

• Workbook: The workbook example has 4 worksheets. Each worksheet corresponds to 1 of the regions I list above (Midwest, Northeast, South or East). Additionally:
  • The Midwest source data is formatted as an Excel Table (myTableSource).
  • The cell range containing the Northeast source data is a named cell range (myNamedRangeSource).
• CSV: The CSV file examples only has data corresponding to the South region. This is the same data as that in the South worksheet of the example workbook.
• Text: This is a tab-delimited text file containing (only) data corresponding to the West region. This is the same data as that in the West worksheet of the example workbook.

The source data isn't formatted. Further below, I explain how you can use Power Query to format the data.

If you use the file examples that accompany this Power Query Tutorial, you can't simply refresh the data extracted by Power Query from the main workbook (which has the queries and connections). The path of the source files you download will be different from the one I specified in the workbook example. The result is a data source error.

Therefore, I suggest you recreate the queries yourself by following the step-by-step processes I describe below.

Import data with Power Query (Get & Transform)

In the following sections, I explain the basic process you can follow to import data from an Excel workbook, CSV or text file using Power Query.

The essential steps are similar. Power Query has several options and features you can use while importing data. However, these topics exceed the scope of this Power Query Tutorial for beginners.

#1: Import a single data source from a workbook with Power Query

To import a single data source from an Excel workbook with Power Query, follow these 6 steps:

1. Go to Ribbon > Data > Get Data > From File > From Workbook.
2. Power Query displays the Import Data dialog box.
3. Identify the source workbook and double-click on it.
4. Power Query displays the Navigator dialog box.
5. Select the data source you want to work with.
6. Click Load.

Example of how to import a single data source from a workbook with Power Query

The following GIF illustrates the process to import a single data source from an Excel workbook with Power Query.

#2: Import data from multiple data sources in a workbook with Power Query

To import data from multiple data sources within a workbook with Power Query, follow these 7 steps:

1. Go to Ribbon > Data > Get Data > From File > From Workbook.
2. Power Query displays the Import Data dialog box.
3. Identify the source workbook and double-click on it.
4. Power Query displays the Navigator dialog box.
5. Select “Select multiple items”.
6. Select the data sources you want to work with.
7. Click Load.

Example of how to import data from multiple data sources in a workbook with Power Query

The following GIF illustrates the process to import data from multiple data sources in a workbook with Power Query.

#3: Import data from a CSV file with Power Query

To import data from a CSV file to Excel with Power Query, follow these 5 steps:

1. Go to Ribbon > Data > Get Data > From Text/CSV.
2. Power Query displays the Import Data dialog box.
3. Identify the source CSV file and double-click on it.
4. Power Query displays a dialog box named after the CSV file.
5. Click Load.

Example of how to import data from a CSV file with Power Query

The following GIF illustrates the process to import data from a CSV file with Power Query.

#4: Import data from a text file with Power Query

To import data from a text file to Excel with Power Query, follow these 5 steps:

1. Go to Ribbon > Data > Get Data > From Text/CSV.
2. Power Query displays the Import Data dialog box.
3. Identify the source text file and double-click on it.
4. Power Query displays a dialog box named after the text file.
5. Click Load.

Example of how to import data from a text file with Power Query

The following GIF illustrates the process to import data from a text file with Power Query.

General considerations about the process to import data with Power Query

Begin the process of importing data with Power Query

You begin the process of creating a query with Power Query as follows:

• When the source data is in an Excel workbook:
  • Go to Ribbon > Data > Get Data > From File > From Workbook; or
  • Use the keyboard shortcut “Alt, A, PN, F, W”.
• When the source data is in a text or CSV file:
  • Go to Ribbon > Data > Get Data > From Text/CSV; or
  • Use the keyboard shortcut “Alt, A, FT”.

Working with the Import Data dialog box

The Import Data dialog box is similar to other dialog boxes you work with when carrying out common Excel processes, such as opening or saving a workbook. Therefore, you can do the following:

1. Browse to the folder where the file (workbook, CSV or text) is saved.
2. Double-click on the appropriate file.

The file-filtering criteria applied by Excel depend on the type of file you're working with (Excel workbook vs. CSV or text). Therefore:

• When you work with an Excel workbook:
  • The Import Data dialog box usually displays any type of Excel file. This includes, for example, regular Excel workbooks, templates and macro-enabled workbooks.
  • Regardless of the precise Excel file type you work with, Power Query limits itself to working with data in cells. Therefore, items such as PivotTables, charts or macros aren't imported.
• When you work with a CSV file: The Import Data dialog box displays only CSV files.
• When you work with a text file: The Import Data dialog box displays only text files.

Working with the Navigator dialog box

Power Query displays the Navigator dialog box when you work with an Excel workbook. The Navigator dialog box has 3 main sections:

1. Left: Available data sources.
2. Right: Preview.
3. Right bottom: Load, Edit and Cancel buttons.

Available data sources

On the left side of the Navigator dialog box, Power Query lists the data sources you can select inside the workbook you're working with (selected with the Import Data dialog box).

When working with an Excel workbook, these data sources are generally 1 of the following 3:

1. A worksheet.
2. An Excel Table.
3. A named range. It's possible to connect Power Query to, for example, dynamic named ranges. The process to work with this type of named range, however, differs from what I describe in this Power Query Tutorial.

You can distinguish these different data sources based on the icon displayed by Power Query next to the source.

If you want to select multiple data sources from a single workbook:

1. Mark the checkbox next to “Select multiple items” on the top left side of the Navigator dialog box prior to selecting the data sources.
2. Once you enable the option to select multiple items, Power Query displays checkboxes to the left of all data sources in the workbook. Use these checkboxes to select all the data sources you want to import.

Preview

Once you select a data source from the list of available data sources, Power Query displays a preview of the data.

Use this preview to confirm that the source data you chose is correct.

Load, Edit and Cancel buttons

Use these buttons to do any of the following:

• Load: Load the data. You can specify how and where the data is loaded by following the process I describe further below.
• Edit: Launch the Query Editor and edit your query. You learn the basics of working with the Query Editor further below.
• Cancel: Close the dialog box and cancel the process of importing data with Power Query.

Working with the dialog box named after the source CSV or text file

Power Query displays a dialog box named after the source file when you work with CSV or text files. This dialog box has 3 main sections:

1. Top: Drop-down menus.
2. Middle: Preview.
3. Bottom: Load, Edit and Cancel buttons.

The main difference between this dialog box and the Navigator dialog box (in a previous section) is the fact that the Navigator dialog box allows you to choose from the available data sources within a workbook.

CSV and text files contain text data only. You don't have named ranges, Excel Tables, nor multiple worksheets to choose from. Therefore, when importing data from a CSV or text file, you don't select a data source within the file.

Results of importing data with Power Query

Results when you import a single data source from a workbook, or a CSV or text file with Power Query

The results of importing data with Power Query are similar when you import data from either of the following:

• A single data source from a workbook.
• A CSV file.
• A text file.

After you complete the appropriate process (described in previous sections), Excel does the following:

1. Loads the imported data to an Excel Table in a new worksheet.
2. Displays the Queries & Connections task pane. This task pane includes the query you just created.

Results when you import data from multiple data sources in a workbook with Power Query

After you complete the process I describe above to import data from multiple data sources in a workbook, Excel does the following:

1. Loads the imported data to the Data Model.
2. Displays the Queries & Connections task pane. This task pane has separate queries for each data source you selected.

Edit a query with Power Query

Power Query has several features that allow you to edit queries. This section introduces the topic. However, covering all options exceeds the scope of this Power Query Tutorial.

The processes I explain below build on the basic procedures to import data (from workbooks, CSV and text files) I explain in previous sections. Please refer to those sections as needed.

#1: Edit a query when importing data from a workbook with Power Query

To edit a query and import data from a workbook with Power Query, follow these 9 steps:

1. Go to Ribbon > Data > Get Data > From File > From Workbook.
2. Power Query displays the Import Data dialog box.
3. Identify the source workbook and double-click on it.
4. Power Query displays the Navigator dialog box.
5. Select the data source you want to work with.
6. Click Edit.
7. Power Query launches the Query Editor.
8. Edit your query.
9. Go to Ribbon > Home > Close & Load.

Example of how to edit a query when importing data from a workbook with Power Query

The following GIF illustrates the process to edit a query when importing data from a workbook with Power Query. In this example, I set the data type of the first column as Date by following the process I describe further below.

#2: Edit a query when importing data from a CSV file with Power Query

To edit a query and import data from a CSV file, follow these 8 steps:

1. Go to Ribbon > Data > Get Data > From Text/CSV.
2. Power Query displays the Import Data dialog box.
3. Identify the source CSV file and double-click on it.
4. Power Query displays a dialog box named after the CSV file.
5. Click Edit.
6. Power Query launches the Query Editor.
7. Edit your query.
8. Go to Ribbon > Home > Close & Load.

Example of how to edit a query when importing data from a CSV file with Power Query

The following GIF illustrates the process to edit a query when importing data from a CSV file with Power Query. In this example, I set the data type of the first column as Date by following the process I describe further below.

#3. Edit a query when importing data from a text file with Power Query

To edit a query and import data from a text file, follow these 8 steps:

1. Go to Ribbon > Data > Get Data > From Text/CSV.
2. Power Query displays the Import Data dialog box.
3. Identify the source text file and double-click on it.
4. Power Query displays a dialog box named after the text file.
5. Click Edit.
6. Power Query launches the Query Editor.
7. Edit your query.
8. Go to Ribbon > Home > Close & Load.

Example of how to edit a query when importing data from a text file with Power Query

The following GIF illustrates the process to edit a query when importing data from a text file with Power Query. In this example, I set the data type of the first column as Date by following the process I describe further below.

General considerations about the process to edit a query with Power Query

Begin the process of editing a query with Power Query

You begin the process of editing a query with Power Query by clicking on the Edit button on the bottom right section of the appropriate dialog box.

Edit your query with the Query Editor

The Query Editor is displayed in a new window and usually has 4 main sections:

1. Ribbon.
2. Formula bar.
3. Preview pane.
4. Query Settings task pane.

You use the Query Editor to edit your data prior to completing the import process with Power Query. Covering all the edition possibilities you have exceeds the scope of this Power Query Tutorial.

At a basic level, your goal with the Query Editor is to do the following:

1. Determine the elements of the source data you work with. This usually involves working with the columns displayed in the Preview pane of the Query Editor.
2. Carry out the editions that are required to shape, clean and transform the source data into the data you need.

Set a column's data type with the Query Editor

In this Power Query Tutorial, I cover a single query edition: Setting a column's data type.

To set a column's data type with Power Query, follow these 3 steps from within the Query Editor:

1. Click on the column whose data type you want to set.
2. Choose the appropriate data type by doing either of the following:
   • Go to Ribbon > Home > Data Type.
     
   • Right-click on the column header and, in the context menu, go to Change Type. This is the process I follow in the example below.
3. If the column you work with has an existing data type conversion, confirm whether you want to:
   • Replace the existing conversion; or
   • Add the new conversion as a separate step.

The following GIF illustrates the process to set a column's data type as Date.

In some cases, such as when working with international date and number formats, you may have to carry out additional steps.

For example, when I apply the process above to the CSV or text file source data examples, the Query Editor returns errors.

To set a column's data type with Power Query when working with international date and number formats, you can usually follow these 5 steps from within the Query Editor:

1. Click on the column whose data type you want to set.
2. Right-click on the column header and, in the context menu, go to Change Type > Using Locale.
3. The Query Editor displays the Change Type with Locale dialog box.
4. Select the appropriate data type and locale using the drop-down lists in the Change Type with Locale dialog box.
5. Click OK.

The following GIF illustrates the process to set a column's data type as Date using Locale (German).

Results of editing a query with the Query Editor

The results of importing data and editing a query with Power Query are similar to those of just importing data (without editing the query).

Results when you edit a query while importing a single data source from a workbook, or a CSV or text file with Power Query

The results of importing data and editing a query with Power Query are similar when you import data from either of the following:

• A single data source from a workbook.
• A CSV file.
• A text file.

After you complete the appropriate process (described in previous sections), Excel does the following:

1. Loads the imported data to a new worksheet.
2. Displays the Queries & Connections task pane. This task pane includes the query you just created.

Results when you edit a query while importing data from multiple data sources in a workbook with Power Query

After you complete the process I describe above to edit a query while importing data from multiple data sources in a workbook, Excel does the following:

1. Loads the imported data to the Data Model.
2. Displays the Queries & Connections task pane. This task pane has separate queries for each data source.

Load the data you import with Power Query

How Power Query loads your data by default

The processes I describe in previous sections work with the default data-loading settings. These settings may vary depending on the source data you work with. For example:

• If you load data from a single data source in a workbook, or a CSV or text file: Power Query loads the data to a new worksheet.
• If you load data from multiple data sources in a workbook: Power Query loads the data to the Data Model.

Specify where and how Power Query loads your data

You can specify where and how Power Query loads the data you import. You specify data-loading settings from either of the following:

• The Navigator dialog box (when working with an Excel workbook) or the dialog box named after the source CSV or text file (when working with a CSV or text file).
• The Query Editor.

The processes I explain below build on the basic procedures to import data (from workbooks, CSV and text files) I explain in previous sections. Please refer to those sections as needed.

#1: Specify where and how imported data is loaded from the Navigator dialog box or the dialog box named after the source CSV or text file

To specify where and how Power Query loads the data you import using the Navigator dialog box or the dialog box named after the source CSV or text file, follow these 4 steps:

1. Go to Load > Load To.
2. Excel displays the Import Data dialog box.
3. Select the loading settings you want to apply. I explain how you work with the Import Data dialog box below.
4. Click OK.

Example of how to specify data-loading settings from the Navigator dialog box or the dialog box named after the source CSV or text file

The following GIF illustrates the process to specify where and how imported data is loaded from the Navigator dialog box or the dialog box named after the source CSV or text file. In this example:

• I work with a CSV file.
• Instead of loading the imported data to a worksheet (the default), I only create the connection.

#2: Specify where and how imported data is loaded from the Query Editor

To specify where and how Power Query loads the data you import using the Query Editor, follow these 4 steps:

1. Go to Home > Close & Load > Close & Load To.
2. Excel displays the Import Data dialog box.
3. Select the loading settings you want to apply. I explain how you work with the Import Data dialog box below.
4. Click OK.

Example of how to specify data-loading settings from the Query Editor

The following GIF illustrates the process to specify where and how imported data is loaded from the Query Editor. In this example:

• I work with a text file.
• Instead of loading the imported data to a worksheet (the default), I only create the connection.

Work with the Import Data dialog box

The Import Data dialog box has 3 main sections, where you specify the following:

1. How you view the imported data in the Excel workbook.
   • As an Excel Table.
   • As a Pivot Table Report.
   • As a Pivot Chart.
   • Only as a connection.
2. Where (in the Excel workbook) is the data loaded.
   • In an existing worksheet.
   • In a new worksheet.
3. Whether the data is added to the Data Model.

Results of specifying where and how imported data is loaded with Power Query

After you complete the appropriate process (described in previous sections), Excel does the following:

1. Loads the imported data according to the settings you specify in the Load To dialog box.
2. Displays the Queries & Connections task pane. This task pane includes the query/queries you just created.

In the examples above, I only created a connection. Therefore, the imported data isn't loaded to a worksheet. The queries, however, are displayed in the Queries & Connections task pane.

Combine the data you import from different files with Power Query

There are different ways in which you can combine the data you import from different files with Power Query. In this Power Query Tutorial, I explain 2 common (and similar) methods to consolidate imported data:

1. Create a new query.
2. Append data to an existing query.

You combine or consolidate data imported from different files with the Append feature of Power Query. When you append data, you add the entries from 1 table to the end of another table. This results in a single table that combines or consolidates different sources of data.

#1: Combine the data you import from different files by creating a new query

To combine the data you import from different files by creating a new query with Power Query, follow these 7 steps:

1. Create individual queries for each data source.
2. Go to Ribbon > Data > Get Data > Combine Queries > Append.
3. Excel displays the Append dialog box.
4. Select the number of tables to append and the tables to append.
5. Click OK.
6. Excel launches the Query Editor.
7. Go to Ribbon > Home > Close & Load.

Example of how to combine data imported from different files by creating a new query

The following GIF illustrates the process to combine data imported from different files by creating a new query.

#2: Combine the data you import from different files by appending data to an existing query

To combine the data you import from different files by appending data to an existing query with Power Query, follow these 9 steps:

1. Create individual queries for each data source.
2. Go to Ribbon > Data > Queries & Connections.
3. Go to the Queries & Connections task pane and right-click on the first query you want to combine.
4. Select Edit in the context menu displayed by Excel.
5. Go to the Query Editor Ribbon > Home > Append Queries.
6. Excel displays the Append dialog box.
7. Select the number of tables to append and the tables to append.
8. Click OK.
9. Go to Ribbon > Home > Close & Load.

Example of how to combine data imported from different files by appending data to an existing query

The following GIF illustrates the process to combine data imported from different files by appending data to an existing query.

General considerations about the process to combine the data you import from different files with Power Query

Begin the process of combining imported data from different files with Power Query

The Append feature generally works with existing queries. Therefore, you must usually go through the process of creating the appropriate queries prior to combining or consolidating the data from different sources. To learn how to create a query, please refer to the appropriate sections in this Power Query Tutorial.

After you create the appropriate queries, you begin the process of combining or consolidating the imported data as follows:

1. If you're creating a new query:
   • Go to Ribbon > Data > Get Data > Combine Queries > Append; or
   • Use the keyboard shortcut “Alt, A, PN, Q, A”.
2. If you're appending data to an existing query:
   • Go to Ribbon > Data > Queries & Connections; or
   • Use the keyboard shortcut “Alt, A, O”.

Work with the Queries & Connections task pane

When you append data to an existing query, you identify the first query you want to combine in the Queries & Connections task pane.

This step is important because, after you complete the process of combining imported data from different files by appending data to an existing query, the query you select in the Queries & Connections task pane contains the data from all the queries you're combining.

Work with the Append dialog box

The Append dialog box has 2 main sections, where you specify the following:

1. The number of tables to combine.
   • 2.
   • 3 or more.
2. The tables that Power Query appends.

The Append dialog box looks different depending on whether you're creating a new query or appending data to an existing query.

• When you create a new query, the Append dialog box looks as follows:
  
• When you append data to an existing query, the Append dialog box looks as follows:
  

In both cases, follow these 3 steps when working with the Append dialog box:

1. Specify the number of tables to append.
2. Specify the tables that are appended.
3. Click OK on the lower right corner of the Append dialog box.

Specify the number of tables to append

To specify the number of tables to append, select the appropriate option (Two tables, or Three or more tables) at the top of the Append dialog box.

Specify the tables to append when you create a new query by combining 2 tables

When you create a new query by appending 2 tables, use the 2 drop-down lists (Primary table and Table to append to the primary table) to select the tables to append.

Specify the tables to append when you create a new query by combining 3 or more tables

When you create a new query by appending 3 or more tables, the Append dialog box displays the following 5 elements:

1. Available table(s) list box: Lists the queries in the workbook you're working with.
2. Tables to append list box: Lists the queries to be combined.
3. Add>> button:
   • Click Add>> to add a table from the Available table(s) list box to the Tables to append list box.
   • Add>> is only enabled if a table in the Available table(s) list box is currently selected.
4. Arrow buttons:
   • Click the Up or Down Arrow to modify the order of the tables in the Tables to append list box. This determines the order in which the data is displayed in the output.
   • Arrows are only enabled if: (i) a table in the Tables to append list box is selected; and (ii) there are at least 2 tables in the Tables to append list box.
5. Delete button:
   • Click Delete to delete a table from the Tables to append list box.
   • Delete is only enabled if a table in the Tables to append list box is selected.

To specify a table to append, follow these 3 steps:

1. Select the table to append in the Available table(s) list box.
2. Click Add>>.
3. Repeat steps #1 and #2 for each individual table you want to append.

Specify the tables to append when you combine 2 tables by appending data to an existing query

When you append a table to an existing query, use the Table to append drop-down list to select the table to append.

The first table (that to which the table you select is appended) is the one you select in the Queries & Connections task pane at the beginning of the process. Power Query identifies this table by adding “(Current)” to the label.

Theoretically, you can select the table labeled as (Current) in the Table to append drop-down list. This, however, results in appending a table to itself and duplicating all records.

Specify the tables to append when you combine 3 or more tables by appending data to an existing query

When you append more than 1 table to an existing query, the Append dialog box displays the following 5 elements:

1. Available table(s) list box:
   • Lists the queries in the workbook you're working with.
   • Power Query identifies the first table (that to which tables are appended) by adding “(Current)” to the label.
2. Tables to append list box:
   • Lists the queries to be combined.
   • The first table (that to which tables are appended) is already displayed in this list box. Power Query identifies this table by adding “(Current)” to the label.
3. Add>> button:
   • Click Add>> to add a table from the Available table(s) list box to the Tables to append list box.
   • Add>> is only enabled if the Available table(s) list box is currently selected.
4. Arrow buttons:
   • Click the Up or Down Arrow to modify the order of the tables in the Tables to append list box. This determines the order in which the data is displayed in the output.
   • Arrows are only enabled if: (i) a table (other than the (Current) table) in the Tables to append list box is selected; and (ii) there are at least 3 tables (the (Current) table + 2 other tables) in the Tables to append list box.
5. Delete button:
   • Click Delete to delete a table from the Tables to append list box.
   • Delete is only enabled if a table (other than the (Current) table) in the Tables to append list box is selected.

To specify a table to append, follow these 3 steps:

1. Select the table to append in the Available table(s) list box.
2. Click Add>>.
3. Repeat steps #1 and #2 for each individual table you want to append to the existing query.

Theoretically, you can add the table labeled as (Current) to the Tables to append list box. This, however, results in appending a table to itself and duplicating all records.

Results of combining the data you import from different files with Power Query

The results of combining the data you import from different files depend on whether you create a new query or append data to an existing query.

Results when you combine the data you import from different files by creating a new query

After you complete the process to combine the data you import from different files by creating a new query, Excel does the following:

1. Loads the imported data to a new worksheet.
2. Displays the Queries & Connections task pane. This task pane includes the query you just created.

Results when you combine the data you import from different files by appending data to an existing query

After you complete the process to combine the data you import from different files by appending data to an existing query, Excel does the following:

1. Loads the imported data to the worksheet containing the existing query you started with ((select at the beginning of the process).
2. Continues to display the Queries & Connections task pane.

In this case, the query you start with contains the combined data from all the tables you appended.

Learn more about Power Query (Get & Transform)

You can get immediate free access to the example files that accompany this Power Query Tutorial by clicking the button below.