presentation of excel and power query

Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

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

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 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 .

Table of Contents

Related Tutorials

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

• Learn how to work with keyboard shortcuts here .
• Learn 350+ Excel keyboard shortcuts here .
• Learn several ways to convert PDF files to Excel here .
• Learn the basics of cleaning data by working with Text functions here .
• 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

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

• Excel workbooks.
• CSV (comma-separated value) files.
• 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:

• Excel workbook.

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.
• Surface Pro.
• Surface Laptop.
• Surface Book 2.
• Surface Studio.
• Xbox One X.
• 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.

• 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:

• Go to Ribbon > Data > Get Data > From File > From Workbook.
• Power Query displays the Import Data dialog box.
• Identify the source workbook and double-click on it.
• Power Query displays the Navigator dialog box.
• Select the data source you want to work with.
• 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:

• Select “Select multiple items”.
• Select the data sources you want to work with.

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:

• Go to Ribbon > Data > Get Data > From Text/CSV.
• Identify the source CSV file and double-click on it.
• Power Query displays a dialog box named after the CSV file.

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:

• Identify the source text file and double-click on it.
• Power Query displays a dialog box named after the text file.

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:

• Go to Ribbon > Data > Get Data > From File > From Workbook; or
• Use the keyboard shortcut “Alt, A, PN, F, W”.
• 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:

• Browse to the folder where the file (workbook, CSV or text) is saved.
• 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:

• 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:

• Left: Available data sources.
• Right: Preview.
• 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:

• A worksheet.
• An Excel Table.
• 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 :

• Mark the checkbox next to “Select multiple items” on the top left side of the Navigator dialog box prior to selecting the data sources.
• 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.

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:

• Top: Drop-down menus.
• Middle: Preview.
• 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:

• Loads the imported data to an Excel Table in a new worksheet.
• 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:

• Loads the imported data to the Data Model.
• 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:

• Click Edit.
• Power Query launches the Query Editor.
• Edit your query.
• 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:

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:

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:

• Formula bar.
• Preview pane.
• 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:

• 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.
• 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:

• Click on the column whose data type you want to set.

• 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.
• 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:

• Right-click on the column header and, in the context menu, go to Change Type > Using Locale.
• The Query Editor displays the Change Type with Locale dialog box.
• Select the appropriate data type and locale using the drop-down lists in the Change Type with Locale dialog box.

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:

• Loads the imported data to a new worksheet.

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:

• 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.

#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:

• Go to Load > Load To.
• Excel displays the Import Data dialog box.
• Select the loading settings you want to apply. I explain how you work with the Import Data dialog box below.

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:

• Go to Home > Close & Load > Close & Load To.

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.

Work with the Import Data dialog box

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

• As an Excel Table.
• As a Pivot Table Report.
• As a Pivot Chart.
• Only as a connection.
• In an existing worksheet.
• In a new worksheet.
• Whether the data is added to the Data Model.

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

• Loads the imported data according to the settings you specify in the Load To dialog box.
• 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:

• Create a new query.
• 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:

• Create individual queries for each data source.
• Go to Ribbon > Data > Get Data > Combine Queries > Append.
• Excel displays the Append dialog box.
• Select the number of tables to append and the tables to append.
• Excel launches the Query Editor.

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:

• Go to Ribbon > Data > Queries & Connections.
• Go to the Queries & Connections task pane and right-click on the first query you want to combine.
• Select Edit in the context menu displayed by Excel.
• Go to the Query Editor Ribbon > Home > Append Queries.

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:

• Go to Ribbon > Data > Get Data > Combine Queries > Append; or
• Use the keyboard shortcut “Alt, A, PN, Q, A”.
• 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:

• 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.

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

• Specify the number of tables to append.
• Specify the tables that are appended.
• 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:

• Available table(s) list box: Lists the queries in the workbook you're working with.
• Tables to append list box: Lists the queries to be combined.
• 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.
• 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.
• 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:

• Select the table to append in the Available table(s) list box.
• Click Add>>.
• 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:

• 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.
• 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.
• Add>> is only enabled if the Available table(s) list box is currently selected.
• 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.
• Delete is only enabled if a table (other than the (Current) table) in the Tables to append list box is selected.

• 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:

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:

• Loads the imported data to the worksheet containing the existing query you started with ((select at the beginning of the process).
• 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 .

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA . Here are some of my most popular Excel Training Resources:

• Free Excel VBA Email Course
• Excel Macro Tutorial for Beginners
• Excel Power Query (Get and Transform) Tutorial for Beginners
• Excel Keyboard Shortcut Cheat Sheet
• Excel Resources

• Ablebits blog

Power Query in Excel tutorial: how to get, use and auto-refresh

This comprehensive Power Query tutorial is your entry point to mastering advanced data transformations in Excel. You will learn how to import data from diverse sources, transform and merge it according to your requirements, write formulas in the M language, automate query refreshes, and more.

In the ever-evolving world, data is essential for practically everything we do, from making crucial business decisions to settling friendly debates about the best pizza toppings. However, information often comes from different sources, in different formats, and sometimes seems as organized as a toddler's toy chest. So, here's the conundrum - how can we import, transform, and analyze data in Excel without spending hours on manual manipulations or writing complex formulas? The solution is Power Query, an invaluable tool for any data analyst, saving hours of time, reducing manual errors, and transforming an unruly informational chaos into a harmonious symphony of insights.

What is Power Query in Excel?

Power Query , also known as Get & Transform , is a multi-purpose tool in Microsoft Excel and Power BI programs that allows you to import data from a wide variety of sources and transform that data to meet your specific needs.

What can Power Query be used for?

Power Query is a versatile tool with a multitude of applications. Here are some common uses:

Data extraction . Connect to various data sources, including databases, Excel files, text files, and online services.

Data transformation . A wide range of data transformation functions are available to reshape your data into a more suitable structure for analysis, such as filtering, sorting, grouping, merging, splitting columns into multiple fields, creating calculated columns, and more.

Data cleansing . Data quality is crucial for accurate analysis. PQ provides tools to identify and correct errors, remove duplicates, trim extra spaces, fill missing values, and standardize formats, ensuring that your data is clean and reliable.

Combining data . PQ makes it simple to combine and merge data from different locations into a single cohesive dataset, facilitating comprehensive analysis.

Automating workflows . Power Query records every step you take, creating a query that can be easily modified or reused later. This is especially useful when dealing with recurring data sources or when you want to keep your analysis up to date with minimal effort. Also, you can refresh your query to get the latest data from the source at any time, on demand or on a schedule.

How to get Power Query in Excel

The Power Query tool is available in all modern versions, spanning from Excel 2010 to Excel 365. Depending on the specific version you are using, you may need to install it as an add-in or access it directly from the ribbon.

Note. . In Excel 2007 and earlier, Power Query is not supported.

How to open Power Query in Excel 2016 – 365

How to add Power Query to Excel 2013 and 2010

In older Excel versions, Power Query is not included by default, but you can download and install it as an add-in from the Microsoft website .

Tip. Be sure to download the correct package for your version of Excel and operating system. To check your version and bitness (32-bit or 64-bit), go to File > Account > About Excel .

Power Query Editor in Excel

The heart and soul of all data transformations lies within the Power Query Editor. It is a separate graphical interface within Excel that plays host to your queries. It is the primary workspace where you'll spend most of your time refining and reshaping your data.

You can access the Editor in two ways:

• On the Data tab, in the Get & Transform group, click Get Data and select Launch Power Query Editor .
• Alternatively, on the Data tab, click the Queries & Connections button to open this pane. Then, double-click a query in the pane to launch the editor.

Here are the six main elements of Power Query Editor:

• Ribbon . At the top of the Editor, you'll find the Ribbon, which organizes various commands and options across five main tabs: File , Home , Transform , Add Column , and View . Each tab has different groups of commands that are relevant to the specific task you want to perform.
• Queries . This is kind of navigations pane on the left side of the editor window that shows all the queries in your workbook. You can use this pane to rename, duplicate, reference, delete, reorder or organize your queries into groups. Just right-click on a query to see available options.
• Data Preview . Positioned front and center, the Data Preview pane provides a snapshot of your dataset after applying each step in your query in real-time. While its primary purpose is to facilitate data inspection, it also allows you to perform some basic transformations, such as sorting and filtering columns, changing data types, etc.
• Properties . On the right-hand side of the editor window, within the Querry Settings pane, you'll find the Properties section. It shows the properties of the selected query, such as its name, description and load settings. Click the All Properties link to view and modify these parameters as needed.
• Applied Steps : Just below the Properties section, there is the Applied Steps area that provides a chronological list of all the actions you've taken during your data transformation. You can use this list to review, edit, reorder, or delete any step in your query. You can also click on a specific step to see how it affects your data in the data preview area.
• Formula Bar . This is the bar at the top of the data preview area that shows the formula or expression for the selected step in your query. You can use this bar to edit or write your own formulas using the Power Query M language.

Advanced Power Query Editor

The Advanced Editor is a text-based interface that allows you to write and edit M code, the language behind Power Query. Think of it as your VIP pass to the Power Query transformation backstage. While the standard PQ interface enables you to reshape data with point-and-click actions, the Advanced Editor gives you precise control over your data transformations.

How to use Power Query in Excel

This section provides an overview of how to use Power Query for data analysis. You will learn the main steps involved in importing, transforming, combining, and exporting data. These steps are:

• Get data – connect to different data sources and load them into Power Query.
• Transform – clean and reshape the data to tailor it to your specific requirements.
• Combine – merge data from various sources into a single, organized dataset.
• Write formulas – craft custom formulas using the Power Query formula language.
• Load – export your final data table to Excel and refresh it manually or automatically.

To start working with your data in Power Query, you need to import it from a source. PQ supports many different data sources such as files, databases, online services, and more.

The three most common sources can be accessed directly from the Excel ribbon - on the Data tab, in the Get & Transform Data group. These primary sources are:

• From Text/CSV - use this option if your data is stored in a text or CSV file.
• From Web - ideal for importing data directly from a web page.
• From Table/Range - suitable for data that's already in Excel, organized within a table or a range.

If you need to import data from other sources, click the Get Data button, and then explore further options:

• From File - import various file types like Excel worksheets, TXT/CSV, XML, JSON, and PDF.
• From Database - connect to databases such as SQL Server or Microsoft Access.
• From Azure - access data from Azure SQL Database, Azure Data Lake Storage, and other Azure services.
• From Power Platforms - import data from Microsoft Power BI services.
• From Other Sources - this category offers even more options, including data from Excel tables/ranges, from the web, OData feeds, ODBC, OLEDB, and others.

For example, to use a CSV file as a data source, you can either click the From Text/CSV button on the ribbon or go a longer way: Get Data > From File > From Text/CSV . If you have multiple CSV files to combine , save them in one folder and choose the From Folder option.

In the next screen, you'll see a preview of the imported data, along with these choices:

• Combine (for multiple files only) - lets you merge different datasets from different sources and file types.
• Load - allows you to load the data directly into an Excel worksheet as a table, pivot table, or connection.
• Transform Data - opens the Power Query Editor, where you can make various transformations, such as filtering, splitting, grouping, pivoting, etc. This is the option you'll use most often.
• Cancel - lets you cancel the import process and close the dialog box.

Transform data

Once your data is loaded into Power Query, you're in the driver's seat to refine it according to your needs. All data transformations are done within a separate interface called the Power Query Editor , where you have four main tabs to explore. You can think of data transformation as sculpting. You start with a raw material that you shape into a form that suits your purpose and that allows you to create appealing reports and dashboards.

Here are some common transformations you can perform using Power Query:

• Sort and filter . Arrange data in ascending or descending order and filter out unwanted rows to focus on the information that matters most.
• Add, remove or duplicate columns . Modify your dataset by adding new columns, removing unnecessary ones, or duplicating existing columns when needed.
• Change data type . You can specify the type of data in each column, such as text, number, date, time, etc.
• Transpose row to column and vice versa . Change the orientation of your data, transforming rows into columns for better analysis and visualization.
• Remove duplicate rows . You can eliminate any rows that have identical values in all columns to reduce the size of your data and to avoid double-counting.
• Trim and clean . Remove leading or trailing spaces from your text values, or any extra characters that are not part of the data to improve the quality and readability of your dataset.
• Extract values . Extract parts of your text values based on a delimiter, position, or pattern. For example, you can extract the first name from a full name column, or the domain name from an email address column.
• Split column . Divide a column into multiple columns based on a delimiter or a specific pattern. For example, you can split a date column into year, month, and day columns.
• Replace or remove values or errors . You can replace any values or errors in your data with another value, such as a blank, zero, or custom value. You can also delete any rows that contain errors or missing values.
• Add conditional column . Create new columns based on predefined conditions, allowing for dynamic data categorization. For example, you can create a column that assigns a category based on a numerical range.
• Add column from example. Utilize examples to guide Power Query in creating new columns with desired data patterns. PQ will try to infer the logic behind your examples and apply it to the rest of the rows.
• Group and summarize data . Aggregate data by groups, calculate sums, averages, or other statistical measures for deeper insights.

These transformations empower you to sculpt your data into a work of art that's not only functional but also elegant and insightful. For detailed instructions, refer to this article: How to use Power Query in Excel – practical examples .

Note. All modifications you make in the editor, collectively forming a query, constitute a new representation of the original data. When you refresh a query, each step runs automatically, eliminating the need to manually repeat the same actions. However, to use your refined data in Excel, you must load it into your workbook .

Merge and combine

One of the most useful features of Power Query is the ability to combine data from different sources into a single table. There are two main ways to do this: append and merge.

• Appending adds rows from one table to another, as long as they have the same columns. For example, you can append sales data from different regions or months into a single table.
• Merging joins two tables based on one or more common columns (unique identifies) like a SQL join. For example, you can merge students data from different courses based on a student name or ID column.

Write formulas in Power Query

Similar to using functions and formulas in Excel worksheets, PQ also has its own set of functions to perform various calculations. These formulas are written in the Power Query formula language, also known as M. Now, here's the great news: you don't need to learn the intricacies of M language syntax to build your custom formulas. Unlike traditional coding, Power Query makes it wonderfully simple and user-friendly using a visual interface.

Let’s take an example. Imagine you want to create a column that calculates the net donation amount after tax deduction based on the values in the Donation and Tax Rate columns. The steps are:

• Go to the Add Column tab and click on Custom Column .
• In the Custom Column dialog box, enter “Net Donation” or any name you want for the New Column Name

=[Donation] * (1 - [Tax Rate])

• As you enter a formula, Power Query checks it, and if no errors are found, displays the message “No syntax error have been detected”. If there’s an error in your formula, it will show an error message indicating the error type.
• Click OK, and the formula will be applied to all rows in the new column.

Load data to Excel

All the transformations you apply to your data connections collectively constitute a query, which is a new representation of the original (and unchanged) data source. When you refresh a query, each step runs automatically. Queries replace the need to manually connect and shape data in Excel.

Once you've performed the necessary data transformations within Power Query, the final step is to load the resulting data into your Excel workbook. You have two options:

• From the Power Query Editor - use the Close and Load commands on the Home tab.
• From the Queries & Connections pane in Excel sheet - right-click a query and choose Load To .

The following steps show you how to do it from the Power Query Editor:

• In the Query pane on the left, select the query you want to load.
• Close and Load – loads the query as a table into a new worksheet (default settings).
• Close and Load To… – lets you specify the format and location for the output.

Excel Power Query refresh

When working with Power Query, you may need to refresh your queries from time to time to get the latest data. Here are some ways to do that:

Refresh Power Query preview

To update the data displayed in the Power Query preview, simply click the Refresh Preview button on the Home tab of the editor:

• Refresh Preview – updates the preview results for the current query.
• Refresh All – updates the preview results for all queries in the editor.

This will reload the data from the source to reflect any changes made to your original data. All the changes you’ve made to the query steps will be applied to the new data automatically.

Refresh Power Query results in Excel

To refresh the Power Query results that have been loaded into an Excel workbook, do one of the following:

• To update a specific query , right-click the query name in the Queries & Connections pane and select Refresh .
• To refresh all queries in the workbook, press the Ctrl + Alt + F5 shortcut. Or, click the Refresh All button on the Data tab, in the Queries & Connections

How to auto refresh Power Query without opening the file

If you need Power Query in Excel to refresh automatically without having to open the source file, you can schedule these refreshes at specified intervals. Here's how to set it up:

• On the Data tab, click on Queries & Connections . This will open the Queries and Connection pane on the right side of the Excel window.
• In the pane, right click a query that you want to schedule for automatic refresh, and then select Properties .
• In the Query Properties dialog box, go to the Usage tab and check the box labeled Refresh every … minutes and enter the number of minutes.
• Make sure the Enable background refresh option is checked.
• To refresh the query when you open your Excel file, select the Refresh data when opening the file check box (unselected by default).
• Click OK to save the changes.

That’s it! From now on, your query will be refreshed automatically every time you open the workbook or at the specified time interval, whichever comes first. This way, you can always have the latest data in your Excel file without manually refreshing it.

These are the essentials of Excel Power Query - your secret weapon for making data work for you. Now, you can easily connect to various data sources, transform, combine and clean your data, and then load it into Excel or Power BI. And don't forget the cherry on top – automatic refreshes that keep your data always up-to-date, even when you're not looking :)

More Power Query examples

• Excel Solver tutorial with examples
• Excel Goal Seek for What-If analysis
• How to do linear regression analysis in Excel
• How to make and use data entry form

Table of contents

15 comments

The "How to auto refresh Power Query without opening the file" part is not working for me. Altough in the comments it seems Microsoft has said that this function is not even available? When you say not opening source file, I don't know if you mean not opening the Excel file at all. I need that it doesnt open anything and it auto refreshes in intervals withouth me opening anything. I did the steps you have outlined, but nothing is happening :( can you further explain how can I get this auto refresh to work?

Hello! I have followed all the steps in the instructions. The automatic update without opening the file works for me. Unfortunately, I can't see your file and what you have done.

Hey thanks for fast reply! Yeah sorry wasn't very specific hehe. Ohh but I just don't know what other info I can give D: My query properties look exactly the same as the one outlined in the tutorial. I got the data from a Sharepoint list and can confirm that the changes only occur when I open the Excel file in the desktop option. Is there another secret option I might be missing?

I am using a query From the Web that is set to refresh every 720 minutes (12 hours or twice a day. Every time I open the file it shows the last refresh occurred when I had it open last. It does not look like it is refreshing while the File is closed.

Hello, Is there anayway to download the data used in this topic ?

Hi, How do we solve this kind of error:

An error occurred in the ‘AP_Query (2)’ query. Expression.Error: The column 'Jan 23' of the table wasn't found. Details: Jan 23

I am having trouble accessing the tabs on under power Query in Excel. The "Add Column" is greyed out. A number of functions are greyed out. just wondering how I can access these functions?

Hi! I can't see what's going on in your Excel. If there is a question about the formula, I will try to answer.

Hello, I don't think the Power Query refresh in the background without opening the file works

It has certainly not worked for me.

Hello Wonderer,

We double tested this in Excel 365, and it works as described on our side.

I've tried 3 different connections/queries to the same source file (.csv on a local drive).

All 3 connections refresh independently. For the 1st one, the refresh interval is set to 3 minutes, for the 2nd one - for 2 minutes, for the 3rd one - auto-refresh is not set.

For the first two connections, the refresh occurred as scheduled both in the Power Query editor and in the worksheet where the results are loaded, without clicking the Refresh button and without opening the source file.

The 3rd connection was not refreshed automatically.

Is the refresh working when the files are closed? Or just if the files are opened?

I don't believe this option works reliably. We have reached out directly to Microsoft on the issue of the auto refresh not working and have been told that it is not a function that is currently available for excel

I am writing to request a blog post on Excel that covers all of the tabs(and all options present in it), and dialogue box options in the program.

As a student, I am eager to learn more about Excel and how to use it effectively. I believe that a blog post covering all of the features of Excel would be a valuable resource for me and other students.

Please consider it and discuss it among the you content team.

I understand that this is a large request, but I would be grateful for any information that you can provide.

Hello Subroto,

Covering all Excel tabs and all their options would require a book, not a post :) Perhaps, you will find this article helpful: Excel ribbon - guide for beginners

Post a comment

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Power Query documentation

Power Query is the data connectivity and data preparation technology that enables end users to seamlessly import and reshape data from within a wide range of Microsoft products, including Excel, Power BI, Analysis Services, Dataverse, and more.

Power Query Overview

• What is Power Query?
• What are dataflows?

Using Power Query

How-to guide.

• Use Power Query
• Transform data

Working with dataflows

• Create and use dataflows in the Power Platform
• Dataflow licenses
• Dataflow best practices

Advanced Power Query articles

• Query folding
• Query diagnostics
• Error handling

Develop your custom connector

• Custom connector development
• Custom connector certification

Reference content

• Connectors in Power Query
• M language reference

• Introduction to Power Query

The party was already in full swing; I was going to be late. Not fashionably late, just late.

When I finally arrived, rather than the usual “ What time do you call this? “, I received the “ Hey; you made it, that’s amazing. It’s great you could come. Let me show you around “. Nobody looked down at me for being late. They were just pleased that I was there and excited to tell me about everything that was going to happen.

Sounds like a nice party, right? You’re starting to wish you were there too, aren’t you? The good news is… you’ve been invited. Actually, you’ve just arrived.

The party I’m referring to is the group of people who in 2017 already knew and loved Power Query (or Get & Transform as it’s known in Excel 2016 and later). I can’t remember the first time I heard about Power Query, but I do remember it was in the same breath as Power Pivot. Then I heard it was an additional add-in I had to install. My first thought was “ Sounds like too much effort; it’ll probably be rubbish anyway. I don’t really need it. I’ve got VBA; I can do everything I need in VBA. It’ll just be a waste of time “. The irony being that had I looked into Power Query at that moment it would have saved me so much VBA frustration.

Time passed. I noticed that more and more Excel experts were talking about it. Ken Puls, Mynda Treacy, Oz du Soliel were all talking about how amazing Power Query is. I dug in my heels. I thought to myself “ I don’t work in a world of dirty data, I don’t spend hours cleaning and fixing data problems. Plus ‘data transformation’ sounds like something I would need a computer science degree for “.

Excel 2016 was released and Power Query was integrated directly into the main Excel application (though it was now known by the name of Get and Transform). I had no excuse, there was no download or installation required. I opened up the Power Query interface. My instant reaction was “ Too hard, don’t understand it, see it was a waste of time, I knew it would be .”

Twelve months pass, I happened to see some free webinars and YouTube videos, no harm in checking them out – BOOM!!!! (That was the sound of my mind being blown). My brain starts thinking again; it’s the first rational Power Query thought I’ve had: “ PowerQuery isn’t just for dirty data, it’s for all data! Data transformation can be as simple as unpivoting a table , or combining all the files in a folder . I suddenly realized t his could save me hours of time and a lot of annoying VBA code. Why had nobody told me about this before? … oh wait … they did!”

This is how I finally arrived at the Power Query party. Since 2017, the popularity of Power Query has expanded. Everybody is willing to share their knowledge because the impact of Power Query is just too big. Since 2017, I have immersed myself in Power Query and Power Pivot and now I rely on these tools to save time every day.

A 2020 survey at datachant.com revealed that average Power Query users saved 76 working days per year. That is 30% of work time! That is HUGE!!!!

Unfortunately, if you ask most Excel users if they use Power Query they will look at you blankly, they have no idea what it is. I want to take my part in changing that. In 2018 I wrote a comprehensive Power Query series, now I’m updating that series so that I can keep spreading the Power Query message, and changing lives. Over the next few posts we will be focusing on Power Query.

By the way, if you’re reading this, then chances are you’ve just arrived at the Power Query party too. All I can say to you is: “ Hey, you made it, that’s amazing. It’s great you could come. Let me show you around “. 😁

What does Power Query do?

Power Query is an ETL tool ( “a what?” I hear you say) . ETL stands for Extract, Transform and Load. Let us look at each of those words individually to get a better understanding of what it means.

• Extract – Data can be extracted from a variety of sources; databases, CSV files, text files, Excel workbooks, specific cells on the same worksheet, websites and even some PDFs. Basically, if there is data stored somewhere in a structured or semi-structured format, Power Query can get to it and pull it out.
• Transform – Once the data has been extracted in the previous step, it can be cleaned up (e.g., remove spaces, split columns, change date formats, fill blanks, find and replace) and reshaped (e.g., unpivot, remove columns).  When data is extracted from different sources it is unlikely to be consistent, the transform process optimizes the shape of the data so it is ready for use.
• Load – Once the data has been extracted and transformed, it needs to be put somewhere so that you can use it. From an Excel perspective, it can be pushed into a worksheet, a data model, or another query.

In short, Power Query as an ETL tool, takes data from different sources and turns it into something which we can use.

So. Power Query sounds pretty useful already. But here is the best part. Once the ETL process has been created, it can be run over and over again with a single click. Which can save hours of work every week.

Some examples of what Power Query can do

Does it all sound a bit abstract and confusing so far? Let me share some examples to give you a real taste of what is possible.

Let’s say that every day you receive a CSV file with an updated price list. Normally you would open the CSV, copy the relevant columns into your spreadsheet then use a lot of LEFT and RIGHT formulas to split some text fields, finally, you use a PivotTable to present the information in the correct format.

You could easily undertake the transformation steps in Power Query to extract the CSV data and clean it up. But the best part is you could refresh the data every day, by simply saving the CSV file in the correct folder location, then just clicking refresh. BOOM! It’s a one-click dream!

Each month you receive 30 Excel workbooks from different divisions of the business, all the workbooks are in the same format. You open each spreadsheet and copy the data into a master spreadsheet to consolidate into a master report.

With Power Query, you can easily consolidate the data from all the workbooks with a few clicks in the user interface. You don’t need to open up each worksheet, you don’t need to copy and paste. Then when it comes time to update, just save all the files in the same folder, and consolidate with one click.

Your business has just acquired a new company. As is often the case, their software system is different to the rest of the company. Management currently has no intention of changing IT systems. Your job is to create a summarized sales dashboard for the whole company each week. Sounds like it could be a lot of work.

With Power Query you can import data directly from each system and with the use of a mapping table, create a dashboard that can be refreshed with a single click.

What is the difference between Power Query and Get & Transform?

The average user doesn’t use the word “Query” to describe the tasks they undertake. Put the word “Power” in front and nothing is any clearer. Maybe this is why Microsoft changed the name to Get & Transform in Excel 2016 and placed it in the Data ribbon. I am sure they were trying to give the user more clarity. While it may be labelled Get and Transform in Excel 2016 and later, the name Power Query appears to have stuck with the Excel community.

How difficult is Power Query to learn?

So, how difficult is it to learn Power Query? If you are thinking that you need to be a programmer, or at least an Excel expert, you would be wrong. Power Query has an easy-to-use interface which is designed for everyday users.

There is a bit of a learning curve, but most of that is learning what each button does.

It takes years to become competent in VBA macros, it takes months to become competent in Power Query. Since 80% of what we did with VBA can be achieved with Power Query, then that is the place to focus.

If you want to go deep into Power Query, there is a programming language called “M” which you could learn. But you can harness 99% of the power without needing it at all.

Time to get started

As mentioned earlier, a study in 2020 found average users saving 76 days per year. With that statistic in mind, let’s stop talking about it, and start using it.

Since Power Query has such a life-changing impact, we have a training course that shows how to use it to save enormous amounts of time. Check it out here: https://exceloffthegrid.com/academy

Read more posts in the Introduction to Power Query series

• Get data into Power Query – 5 common data sources
• DataRefresh Power Query in Excel: 4 ways  &  advanced options
• Use the Power Query editor to update queries
• Get to know Power Query Close & Load options
• Power Query Parameters: 3 methods
• Common Power Query transformations (50+ powerful transformations explained)
• Power Query Append: Quickly combine many queries into 1
• Get data from folder in Power Query: combine files quickly
• List files in a folder & subfolders with Power Query
• How to get data from the Current Workbook with Power Query
• How to unpivot in Excel using Power Query (3 ways)
• Power Query: Lookup value in another table with merge
• How to change source data location in Power Query (7 ways)
• Power Query formulas (how to use them and pitfalls to avoid)
• Power Query If statement: nested ifs & multiple conditions
• How to use Power Query Group By to summarize data
• How to use Power Query Custom Functions
• Power Query – Common Errors & How to Fix Them
• Power Query – Tips and Tricks

Discover how you can automate your work with our Excel courses and tools.

Excel Academy The complete program for saving time by automating Excel.

Excel Automation Secrets Discover the 7-step framework for automating Excel.

Office Scripts: Automate Excel Everywhere Start using Office Scripts and Power Automate to automate Excel in new ways.

2 thoughts on “Introduction to Power Query”

I felt the same way about Tables (or Lists as they were originally called). I shunned them for longer than I care to admit, mostly because I didn’t like how they defaulted to the alternating row color theme. Knowing what I know now, I will happily clear that theme as many times as necessary to take full advantage of what Tables have to offer.

I wonder what current features we are complaining about that we will later come to rely on?

I just leave the default colours now, as it’s a useful way to know what each Table is. Or maybe I’m just lazy.

If I’m building an application with a Table as part of the UI, then I will change to another color.

Leave a Comment Cancel reply

Excel Power‌ Query Tutorial (Get & Transform) + Examples

- Written by Puneet

Written by Puneet for Excel 2010, Excel 2013, Excel 2016, Excel 2019

If you are one of those people who work with data a lot, you can be anyone ( Accountant , HR, Data Analyst, etc.), power query can be your power tool.

Let me come straight to the point, Power Query is one of the advanced Excel skills that you need to learn and in this tutorial, you will be exploring power query in detail and will be learning to transform data with it.

Let’s get started.

What is Excel Power Query

Power Query is an Excel add-in that you can use for ETL. That means, you can extract data from different sources, transform it, and then load it to the worksheet. You can say POWER QUERY is a data cleansing machine as it has all the options to transform the data. It is real-time and records all the steps that you perform.

Why Should You Use Power Query (Benefits)?

If you have this question in your mind, here’s my answer for you:

• Different Data Sources : You can load data into a power query editor from different data sources, like, CSV , TXT, JSON, etc.
• Transform Data Easily : Normally you use formulas and pivot tables for data transformations but with POWER QUERY you can do a lot of things just with clicks.
• It’s Real-Time : Write a query once and you can refresh it every time there is a change in data, and it will transform the new data which you have updated.

Let me share an example:

Imagine you have 100 Excel files that have data from 100 cities and now your boss wants you to create a report with all the data from those 100 files. OKAY, if you decide to open each file manually and copy and paste data from those files and you need at least one hour for this.

But with the power query, you can do it in minutes. Feeling excited? Good.

Further in this tutorial, you will learn how to use Power Query with a lot of examples, but first, you need to understand its concept.

The Concept of Power Query

To learn power query, you need to understand its concept that works in 3 steps:

1. Get Data

Power query allows you to get data from different sources like web, CSV, text files, multiple workbooks from a folder, and a lot of other sources where we can store data.

2. Transform Data

After getting data in the power query you have a whole bunch of options that you can use to transform it and clean it. It creates queries for all the steps you perform (in a sequence one step after another).

3. Load Data

From the power query editor, you can load the transformed data to the worksheet, or you can directly create a pivot table or a pivot chart or create a data connection only.

Where is Power Query (How to Install it)?

Below you can see how to install access to the power query in the different versions of Microsoft Excel.

If you are using Excel 2007, I’m sorry PQ is not available for this version so you need to upgrade to the latest version of Excel (Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010).

Excel 2010 and Excel 2013

For 2010 and 2013, you need to install an add-in separately which you can download from this link and once you install it, you’ll get a new tab in the Excel ribbon, like below:

• First, download the add-in from here (Microsoft’s Official Website).
• Once you have downloaded the file, open it and follow the instructions.
• After that, you’ll automatically get the “Power Query” tab on your Excel ribbon.

If somehow that “POWER QUERY” tab doesn’t appear, there is no need to worry about it. You can add it using the COM Add-ins option.

• Go to File Tab ➜ Options ➜ Add-ins.
• In “Add-In” options, select “COM Add-ins” and click GO.
• After that, tick mark “Microsoft Power Query for Excel”.
• In the end, click OK.

Excel 2016, 2019, Office 365

If you are using Excel 2016, Excel 2019, or you have OFFICE 365 subscription, it’s already there on the Data tab , as a group named “GET & TRANSFORM” (I like this name, do you?).

If you are using Excel in Mac I’m afraid that there is no power query add-in for it and you can only refresh an existing query but you can’t create a new one and or even edit a query ( LINK ).

Power Query Editor

Power Query has its own editor where you can get the data, perform all the steps to create queries, and then load it to the worksheet. To open the power query editor, you need to go to the Data Tab and in the Get & Transform ➜ Get Data ➜ Launch Power Query Editor.

Below is the first look at the editor which you will get when you open it.

Now, let’s explore each section in detail:

Let’s look at all the available tabs:

• File: From the file tab, you can load the data, discard the editor, and open the query settings.
• Home : In the HOME Tab, you have options to manage the loaded data, like, delete and move columns and rows.
• Transform : This tab has all the options which you need to transform and clean the data, like merge columns, transpose, etc.
• Add Column : Here you have the option to add new columns to the data you have in the power editor.
• View : From this tab, you can make changes to the view for the power query editor and data loaded.

2. Applied Steps

On the right side of the editor, you have a query setting pane which includes the name of the query and all the applied steps in a sequence.

When you right-click on a step you have a list of options that you can perform, like, rename, delete, edit, move up or down, etc. and when you click on a step, the editor will take you to the transformation done on that step.

Look at the below where you have the total five steps applied and when I click on the 4th step it takes me to step four’s transformation where the columns name hasn’t changed.

The queries pane on the left side lists all the queries you have in the workbook right now. It’s basically one place where you can manage all the queries.

When you right-click on a query name you can see all the options that you can use (copy, delete, duplicate, etc.)

You can also create a new query by simply right click on the blank space on the queries pane and then select the option for the data source.

4. Formula Bar

As I said, whenever you apply a step in the editor it generates M code for that step, and you can see that code in the formula bar. You can simply click on the formula bar to edit the code.

Once you learn to use M code you can also create step by writing the code and simply clicking on the “FX” button to enter a custom step.

5. Data Preview

The data preview area looks like an Excel worksheet but there’s a little different than a normal worksheet where you can edit a cell or data directly. When you load data into the editor (we will do it in a while) it shows all the columns with the headers with the columns name and then rows with data.

At the top of each column, you can see the data type of the data in the column. When you load data into the editor the power query applies the right data type (almost every time) to each column automatically.

You can click on the top left button on the column header to change the data type applied to the column. It has a list of all the data types from where you can.

And on the left side of the column header there you have the filter button which you can use to filter values from the column. Note: When you filter values from a column, the power query takes it as one step and lists it in the applied steps.

If you right-click on the header of the column you can see that there is a menu that includes a list of the options which you can use to transform the data and use any of the options and PQ stores it as a step in the applied steps.

Data Sources for Power Query

The best part of the power query is you have the option to get data from multiple sources and transform that data and then load it into the worksheet. When you click on the Get Data in the GET & TRANSFORM you can see the complete list of data sources that you can get data load into the editor.

Now let’s look at some of the data sources:

• From Table/Range : With this option, you can load data into the power query editor directly from the active worksheet.
• From Workbook : From a different workbook that you have on your computer. You just need to locate that file using an open dialog box and it will get data from that file automatically.
• From Text/CSV : Get data from a text file or a comma-separated file and then you can load it into the worksheet.
• From Folder : It takes all the files from the folder and load data from them into the power query editor. (See this: Combine Excel Files from a Folder ).
• From Web : With this option, you get data from a web address, imagine you have a File that is stored on the web or you have a web page from where you need to get the data.

How to Load Data into Power Query Editor

Now let’s learn to load data into the power query editor. Here you have a list of student names and their scores ( LINK ).

You will be loading data directly from the worksheet, so you need to open the file first and then follow the below steps:

• First, apply an Excel table to the data (Even if you don’t do it Excel will do it for you before loading data into PQ editor).
• Now, select a cell from the table and click on the “From Table/Range” (Data Tab Get & Transform).
• Once you click on the button, Excel confirms the range of data to apply an Excel table to it.
• At this point, you have the data into the power query editor, and it looks something like below.
• In the Formula bar, PQ has generated the M code for the table you have just loaded into the editor.
• On the left side of the editor, you have the queries pane where you have the list of the queries.
• On the right side, in the query settings, you have the section called “Applied Steps” where you have all the steps listed. Note: You must be thinking that you haven’t performed any “Changed Type” but there’s a step called “Changed Type” is there. Let me tell you the SMARTNESS of POWER QUERY when you load data into the editor it checks and applies the correct data types for all the columns automatically.

Power Query Examples (Tips and Tricks)

You can learn to perform some of the basic tasks which you normally do with functional formulas in Excel, but with power query, you can do it with a few clicks:

1. Replace Values

You have a list of values, and you want to replace a value or some values with something else. Well, with the help of the power query you can create a query and replaces those values, in no time.

In the below list, you need to replace my name “Puneet” with “Punit”.

• First, edit the list in the power query editor.
• After that, in the power query editor, go to “Transform Tab” and click “Replace Values”.
• Now, in “Value to Find”, enter “Puneet” and in “Replace With” enter “Punit” and after that, click OK.
• Once you click OK all the values get replaced with the new values and now, click on “Close and Load” to load data in the worksheet.

2. Sort Data

Just like normal sorting, you can sort data by using power query and I’m using the same name list which you have used in the above example.

• First, load data in the power query editor.
• In the Home tab, you have two sorting buttons (Ascending and Descending).
• Click on any of these buttons to sort.

3. Remove Columns

Let’s say you got data from somewhere and you need to delete some columns from it. The thing is, you have to delete those columns every time you add new data, right? But, power query can take care of this.

• Select the column or multiple columns that you want to delete.
• Now, right-click and select “Remove”.

Quick Tip:  There’s also an option to “Remove Other Columns” where you can delete all the unselected columns.

4. Split Column

Just like the text to column option, you have “Split Column” in power query. Let me tell you how it works.

• Select the column and go to the Home Tab ➜ Transform ➜ Split Column ➜ By Delimiter.
• Select the custom from the drop-down and enter “-” into it.
• Left-most Delimiter
• Right-most Delimiter
• Each occurrence of the delimiter

If you have only one delimiter in a cell, all three will work in the same way, but if you have more than one delimiter then you have to choose accordingly.

5. Rename a Column

You can simply rename a column by right click and then click on the “Rename”.

Quick Tip : Let say you have a query for renaming a column and someone else rename it by mistake. You can restore that name just with a click.

6. Duplicate Column

In Power Query, there is a simple option to create a duplicate column. All you need to do is right-click on the column for which you want to create a duplicate column and then click on “Duplicate Column”.

7. Transpose Column or Row

In the power query, transposing is a cup of cake. Yes, just one click.

• Once you load data into the power query editor, you just need to select the column(s) or row(s).
• Go to Transform Tab ➜ Table ➜ Transpose.

8. Replace/Remove Errors

Normally for replacing or removing errors in Excel you can use find and replace option or a VBA code. But in power query, it’s a whole lot easier. Look at the below column where you have some errors and you can remove as well as replace them.

When you right-click on the column, you’ll have both of the options.

• Replace Errors
• Remove Errors

9. Change Data Type

You have data in a column but it’s not in the right format. So, every time you need to change its format.

• First, edit data into the power query editor.
• After that, select the column and go to the Transform Tab.
• Now, from data type select the “Date” as a type.

10. Add Column from Examples

In the power query, there is an option to add a sample column which is not actually a sample related to the current column.

Let me give you an example:

Imagine you need day names from a date column. Instead of using a formula or any other option, you can use, you can use the “Add Column from Examples”.

Here’s how to do this:

• Right-click on a column and click on “Add Column from Examples”.
• Here you’ll get a blank column. Click on the first cell of the column to get the list of values you can insert.
• Select “Day of Week Name from Date” and click OK.

Boom!  your new column is here.

11. Change Case

You have the following options for changing the case of text in power query.

• Capitalize Each Word

You can do it by right click on a column and select any of the above three options. Or, go to the Transform Tab ➜ Text Column ➜ Format.

12. Trim and Clean

To clear data or delete unwanted spaces you can use TRIM and CLEAN options in power query. Steps are simple:

• Right-click on a column or select all the columns if you have multiple columns.
• TRIM: To remove trailing and leading whitespaces from a cell.
• CLEAN: To remove non-printable characters from a cell.

13. Add Prefix/Suffix

So you have a list of values and from this list, you want to add a Prefix/Suffix in each cell. In Excel, you can use the concatenate method but in power query, there is a simple to use option for both.

• First, select the column where you need to add Prefix/Suffix.
• Then, go to the Transform Tab ➜ Text Column ➜ Format ➜ Add Prefix/ Add Suffix.
• Once you click on one of the options, you’ll get a dialog box to enter text.
• And after entering the text, click OK.

14. Extract Values

If you are a formula savvy, then I’m sure you agree with me that extracting text or number from a cell requires to combine different functions. But power query has solved a lot of these things in a good way. You have seven ways to extract values from a cell.

15. Only Date or Time

It happens a lot of times that you have date and time, both in a single cell, but needs one of them.

• Select the column where you have the date and time combined.
• Date : Right Click ➜ Transform ➜ Date Only.
• Time : Right Click ➜ Transform ➜ Time Only.

16. Combine Date and Time

Now you know how to separate date and time. But the next you need to know how to combine them.

• First, select the date column and click on the “Date Only” option.
• After that, select both columns (Date and Time) and go to the transform tab and from the “Date and Time Column” Group go to Date and click “Combine Date and Time”.

17. Rounding Numbers

Here are the following options which you have for rounding numbers.

• Round Down: To round down a number. 
• Round Up: To round up a number. 
• Round: You can choose up to how my decimals you can round.

Here are the steps:

• Round Down: To round down a number.
• Round Up: To round up a number.
• Round: You can choose up to how my decimals you can round.

Note:  When you select the “#3 Round” option you need to enter the number of decimals to round.

18. Calculations

There are options that you can use to perform calculations (a lot of). You can find all these options on the Transform Tab (in Number Column group).

• Trigonometry
• Information

To perform any of this calculation you need to select the column and then the option.

19. Group by

Let’s say you have a large data set and you want to create a summary table. Here’s what you need to do:

• In the Transform tab, click on the ‘Group by“ button and you’ll get a dialog box.
• Now, from this dialog box select the column with which you want to group and after that, add a name, select the operation, and the column where you have values.

Note:  There are also some advanced options in the “Group by” option which you can use to create a multi-level group table.

20. Remove Negative Values

In one of my blog posts, I have listed seven methods to remove the negative signs  and the power query is one of them. Just right click on a column and go to transform option and then click on “Absolute value”.

This instantly removes all the negative signs from the values.

More Examples

• Perform VLOOKUP in Power Query in Excel
• Unpivot Data using Power Query
• IF Statement in Power Query
• Concatenate Values (Text and Numbers) using Power Query
• Calculate Date Difference using Power Query
• Merge [Combine] Multiple Excel FILES into ONE WORKBOOK
• Remove Duplicates using Power Query
• IF Text Contains in Power Query

How to Load Data Back to the Worksheet

Once you transform your data, you can load it to the worksheet and use it for further analysis. On the home tab there is a button called “Close and Load” when you click on it you get a drop-down which has options further:

• Close and Load
• Close and Load To
• Once you click on the button, it will show the following options:
• Pivot Table Report:
• Pivot Chart
• Only Create Connection
• Existing Worksheet
• New Worksheet.
• Add this data to the Data Model.
• Just select the table option and new worksheet and don’t tick mark the data model and click OK.
• The moment you click OK, it adds a new worksheet with the data.

More Examples to Learn

Auto Refresh a Query

From all the examples that I have mentioned here, this one is the most important. When you create a query, you can make it auto-refresh (you can set a timer).

And here are the steps:

• On the Data tab, click on “Queries & Connections” and you’ll get the Queries and Connection pane on the right side of the window.
• Now, right-click on the query and tick mark “Refresh every” and enter the minutes.

How to use a Formula and a Function in Power Query

Just like you can use functions and formulas in Excel worksheet, the power query has its own list of functions that you can use. The basics of function and formulas in power query are the same as Excel’s worksheet functions.

In PQ, you need to add a new custom column to add a function or a formula. Let’s take an example: In the below data (already in the PQ editor) you have the first name and last name ( DOWNLOAD LINK ) .

Imagine you need to merge both names and create a column for the full name. In this case, you can enter a simple formula to concatenate names from both columns.

• First, go to the Add Column tab and click on the “Custom Column”.
• Now in the custom column dialog box, enter the name of the new column “Full Name” or anything you want to name the new column.
• The custom column formula is the place where you need to enter the formula. So enter the below formula in it:
• When you enter a formula in the “custom column formula”, PQ verify the formula that you have entered and shows a message “No syntax error have been detected” and if there’s an error it will show an error message based on the type of the error.
• Once you enter the formula and that formula doesn’t have any errors in it, simply press OK.
• Now you have a new column at the end of the data which has values from two columns (first name and the last name).

How to use a Function in Power Query

In the same way, you can also use a function while adding a custom column and Power Query has a huge list of functions that you can use.

Let’s understand how to use a function with an easy and simple example. I’m continuing the above example where we have added a new column by combining the first name and last name.

But now, you need to convert that full name text which you have in that column into the upper case. The function which you can use is “ Text.Upper ” . As the name suggests, it converts a text to an upper-case text.

• First, go to the add column tab and click on the custom column.
• Now in the custom column dialog box, enter the column name and below formula in the custom column formula box:
• And when you click OK it creates a new column with all the names in the uppercase.
• The next thing is to delete the old column and rename the new column. So right-click on the first column and select remove.
• In the end, rename the new column his “Full Name”.

There are a total of 700 functions that you can use in power query while adding a new column and here is the complete list provided by Microsoft for these functions, do check them out.

How to Edit a Query in PQ

If you want to make some changes in the query which is already in your workbook you can simply edit it and then make those changes. On the Data tab, there’s a button named Queries and Connections.

When you click on this button, it opens a pane on the right side that lists all the queries that you have in the current workbook.

You can right-click on the query name and select edit and you will get it in the power query editor to edit.

When you edit a query, you can see that all the steps which you have performed earlier are listed in the “Applied Steps” that you can also edit or you can perform new steps.

And once you are done with your changes you can simply click on the “Close & Load” button.

Export and Import Connections

If you have a connection which you have used for a query and now you want to share that connection with someone else, you can export that connection as an odc file.

On the query table, there’s a button called “Export Connection” and when you click on it, it allows you to save that query’s connection in your system.

And if you want to import a connection that is shared by someone else, you can simply go to the Data tab and in the Get & Transform click on the existing connections .

And then click on the “Browse for More” button from where you can locate the connection file which has been shared with you and import it to your workbook.

Power Query Language (M Code)

As I mentioned earlier that for every step you performed in power query it generates a code (at the backend) which is called M Code . On the Home tab, there is a button called “ Advanced Editor” which you can use to see the code.

And when you click on the advanced editor it will show you the code editor and that code looks something like below:

M is a case sensitive language and like all the other languages it uses variables and expressions. The basic structure of code looks like below where the code starts with the LET expression.

In this code, we have two variables and the values defined to them. In the end, to get the value, IN expression has been used. Now when you click OK it will return the value assigned to the variable “Variablename” in the result.

Check out this resource to learn more about Power Query Language.

You can’t afford to avoid the POWER QUERY. If you think like this, a lot of things which we do with Excel functions or VBA codes can be automated using it, and I’m sure this tutorial inspires you to use it more and more.

Power Query is a data transforming engine which you can use to get data from multiple sources, clean and transform that data and then use it further in the analysis.

But now you need to tell me one thing. Which thing do you like most about the POWER QUERY?

You must check out these tutorials

• Create a Pivot Table from Multiple Files

Powerful Introduction to Power Query

For analysts and data wranglers.

Thanks for your interest in learning about Power Query. Please watch below video to understand what is Power Query and how it can help you.

What is Power Query – Video

Workbook & resources for you:.

Please download the workbook showcased in the session from below links.

• Powerful Intro to Power Query – PBIX Workbook
• Introduction to Power Query – Podcast
• Oddly shaped data to a list
• Extract currency amounts from text
• Unpivot data quickly
• More Power Query examples

Online classes for you

If you enjoyed the video, you are going to love my 50 ways to analyze data and Power BI Play Date online classes. Click on below links to know more about them and join the wait list.

• 50 ways to analyze data
• Power BI Play Date

Thank you so much for tuning in. I hope you enjoyed it.

What is Power Query: A Beginner’s Guide [2024]

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Power Query is an ETL (Extract Transform and Load) tool that allows you to import data, transform it and load it into a destination.

If you perform repetitive data transformation tasks or struggle to organize and analyze your data, Power Query might be the solution for you. It has a user-friendly interface that allows you to perform data transformation in a visual way and remembers your transformations for the next time you come back. This means you can set up your logic once, and spend your time analyzing your data, not transforming it again and again.

In this beginner’s guide to Power Query, we’ll take you through the basics of what Power Query is , how it works, and how it can help you transform and analyze your data. By the end of this article, you’ll have a good grasp of Power Query and how it can make your life easier. Let’s get started!

Power Query Capabilities

Benefits of power query, limitations of power query, where to find power query, how power query works, m formula language, the power query editor, important ribbon tabs, connect to data sources, sorting data, filtering data, transforming columns, pivot columns, group or summarize, adding columns, merge or append queries, advanced editor, saving your work, introduction to power query.

So what is Power Query in Power BI and Excel, what can it do, and what are its benefits and limitations? Power Query is a powerful tool from Microsoft used for transforming and preparing data. It allows you to easily connect to and transform data from a variety of sources.

With Power Query, you can make your data clean and organized with just a few clicks. You don’t need to be a computer expert or write a lot of code. To help you transform your data, Power Query uses an intuitive, graphical interface. With every transformation, you see exactly what happens.

In the Power BI ecosystem, the role of Power Query is to perform the extract, transform and load (ETL) of your data. This makes sure the data is in the right shape for your data model .

So what can you do with it?

You can accomplish a wide range of data transformation tasks with Power Query. Here are some of the things you can do with Power Query:

• Connecting to data sources: Power Query makes it easy to connect to data sources, including databases, text files, spreadsheets, and cloud services. Once you connect to your data source, you can preview the data, select what you want to use, and transform it as needed.
• Transforming and cleaning data: Power Query includes a range of built-in transformation functions that allow you to clean, reshape, and transform your data quickly. This means you can remove extra spaces, change formats, remove duplicates and more. You can also write custom functions using the Power Query M formula language.
• Combining and appending data: Power Query lets you combine data from different sources to make one big unified dataset. You can merge tables based on matching columns, or append tables vertically to create a larger data set. This even works to combine multiple files saved within a folder .
• Aggregating data: You can use Power Query to group and summarize data, creating aggregate statistics like counts, sums, averages, and more. This is also useful for summarizing a very large dataset that is otherwise too big to import into Excel.
• Adding columns: With Power Query, you can add new columns to your data set, and populate them with calculated values based on other columns in the data set. This makes it easy to create new metrics and calculations that are based on your existing data.

In summary, Power Query can help you work with data in many different ways. So why would you choose Power Query?

Power Query has many benefits that can make working with data much easier. Here are some of the benefits of using Power Query:

• Easy to use: Power Query is user-friendly, and you don’t need to be an expert in coding to use it. The visual interface makes it easy to see the result of your data transformation.
• Reshape Data Easily: Power Query allows you to transform, clean, and combine data from multiple sources all in one query. You can see the result of each of your transformations visually.
• Data Remains Intact: Power Query builds its logic by referencing your source data, so your original data is not modified. You can always go back to the original data if needed.
• Automate your Data Transformation : With Power Query, you can automate recurring data transformation. You can create a query once and apply it to new data whenever you need to, which saves you time and effort.
• Connect to Data Sources: Power Query has connectors to many data sources, including databases, web pages, cloud services, and more. This means you can easily import data from a variety of sources.
• Custom Functions: When a function is missing or you want to automate multiple steps, you can create custom functions. You can easily save your logic and use it on multiple queries. Each time you need it, you simply call your function.
• Pricing: Power Query is available for free with Excel and Power BI. This means that you don’t need to pay extra for this powerful data transformation tool.

While Power Query has many benefits, there are also some limitations to be aware of. Here are some of the limitations:

• Storing Data : Power Query does not store data physically like a database. This means you will need to load the data into memory each time you use it. You can output the transformed data to a destination, but generally, a refresh will start from scratch.
• Availability: Power Query is not available in every tool, which means that you may need to use a different data transformation tool depending on your needs. A language like SQL is supported in many more tools compared to the Power Query M language.
• Speed : Power Query can be slow when you have many queries in your file, which can lead to frustrating wait times when working with complex datasets.
• Connectors : Power Query has a high number of native connectors but it can’t connect to all data sources. If one is missing, you may need to develop a custom connector.

You can find Power Query across several Microsoft products. It is currently available in two forms.

• Power Query Online : Integrated in places like Power BI Dataflows, Azure Data Factory, Dataverse and many other online web experiences.
• Power Query for Desktop : Used in Microsoft Excel and Power BI Desktop.

So where can you find Power Query in Power BI and Excel (the Desktop experience)?

In Power BI you can find Power Query through the Home tab in the ribbon . You can get there in two ways. Either you:

• Connect to a Data Source: Click the Get Data drop-down. Then select the Data source you want to connect to. After going through the wizard, you now end up in the Power Query Editor.
• Open Power Query directly: You can click the Transform Data table icon to open Power Query. Or alternatively press theTransform Data dropdown and press Transform Data .

Another common place to use Power Query is in Microsoft Excel . Where can you find Power Query in Excel?

To open Power Query in Excel you first navigate to the Data tab , click Get Data and press Launch Power Query Editor .

Alternatively, if you want to connect to a data source directly, you can also select the relevant data source from the same dropdown menu.

Power Query works by first connecting to a data source. You can then extract the required data. Once extracted, you can perform various operations to transform the data— actions such as filtering, sorting, grouping, merging, etc.

In the last stage, you load the transformed data into a destination like:

• an Excel worksheet ,
• the Data Model in Power BI or Power Pivot
• a Dataflow in the Power BI service
• a Sink in Azure Data Factory

So how do the different components in Power Query work together?

To perform transformations Power Query uses a query language called M . The M refers to Mashup language and is designed with the idea of mixing data sources together. The M language is case-sensitive and uses rather descriptive function names.

Power Query’s graphical user interface allows you to perform a lot of operations. When you press a button, an action is performed and the corresponding M-code generates automatically.

More advanced users can also write custom code manually through the formula bar or advanced editor. This allows the user to fulfil even the most complex requirements.

Power Query Components

Power Query consists of different components. At the heart of Power Query is the Power Query Editor, where you will spend most of your time transforming data. Next, we’ll delve into the three most important Ribbon tabs that you will use for data transformations. Finally, we will discuss how to connect to different data sources.

The Power Query Editor is where you do most of your data transformation work. It consists of 7 fundamental components that help you when transforming your data.

• Quick Access Toolbar: A customizable toolbar at the top of the Power Query Editor that gives access to your favourite commands. You can save your favourite operations and access them with a keyboard shortcut .
• The Ribbon: A set of tabs that contains a variety of tools and options for working with data in Power Query. The Ribbon is organized by functional area. You will find the most important prebuilt transformation functions in the Home, Transform and Add Column section.
• Query Pane: A window on the left-hand side of the editor that displays the list of queries in the Power Query Editor. You can use the Query Pane to organize your queries in folders, rename them, add new ones or delete existing ones.
• Formula Bar: A text box located at the top of the editor that displays the M code for the selected query step. You can use the Formula Bar to review the UI-generated formulas, learn their syntax or edit the M code directly. In case the formula bar is invisible, make sure to turn it on in the View tab of the Ribbon.
• Data Preview: A grid that displays a preview of the data in the current query. You can use the Data Preview to view and verify the results of your transformations and ensure your data is clean and properly formatted. You can click the column’s drop-down arrow to rename, filter or sort your columns. Lastly, there are options to perform actions on the entire table by clicking the Table icon in the top-left corner.
• Properties: Part of the Query Settings pane that displays the Query Name.
• Applied Steps: A list that displays the sequence of transformations that have been applied to the data in the current query. You can use the Applied Steps to view and modify the transformations, or to reorder them if needed.

Power Query comes with different ribbon tabs. For your data transformations, you will mostly use the Home, Transform, and Add Column tabs .

Each of these three ribbons is designed with a purpose:

To get started with Power Query, you need first to create a connection to the data source that you want to import data from. You can connect to databases, spreadsheets, text files, webpages, etc., as well as access public datasets located on cloud storage services such as Google Drive and Dropbox.

To connect to a data source you:

• Navigate to the Home Tab .
• Click the drop-down at New Source .
• Select the desired data source .

There are just a few options available in the drop-down. If you’re looking for a different source, make sure to click More at the bottom. There are hundreds of connectors hidden behind that button!

Once the connection is established, the data will land in the data preview pane. You can then begin extracting and transforming the required data.

Transformation Examples

In this section, we’ll learn how to use Power Query to transform data. Transforming data means changing how data looks or behaves so we can use it in better ways. Analysts tend to spend nearly 80% of their time cleaning data and Power Query helps do this in an effective way. Let’s look at some examples in the below tutorial.

In Power Query, you can sort your data, which means putting it in a specific order based on certain rules. Sorting can be done in two ways: ascending or descending. It is easy to sort your data. Simply:

• Click the drop-down arrow in the column header.
• Then select either “ Sort Ascending ” or “ Sort Descending “.

Once you click Sort Ascending, your text values will be sorted alphabetically. You can review the syntax of the generated formula in the picture below.

If you want to sort by multiple columns, simply repeat these steps for the next column and it will be added to the sorting formulas.

The user interface automatically generates the relevant formulas including the Table.Sort function. This means that as a beginner, you don’t need to do any of the complicated work. However, as you become more comfortable, you can apply more advanced sorting algorithms .

Data types are an important part of Power Query that helps you manage and transform your data. In Power Query, data types help you identify the type of data you are working with, such as numbers, text, or dates. This is useful because some operations are supported by one data type and not by another.

There are many different data types available in Power Query. Some of them include dates, times, text, duration, numbers, and logical values. You can easily assign data types to columns.

In the image below, you can tell what data type a column is by looking at the icon on the left side of the column header. If you click the icon, you can choose to transform the column to a different data type.

By performing these steps, the columns are transformed into the data types indicated.

By understanding the different data types available in Power Query, you can transform your data in the desired way. One area where the importance of data types becomes especially clear is when you’re filtering data.

Filtering data allows you to show only the data that meets certain criteria. You can filter data based on specific values, ranges, or other criteria. Let’s look at a few examples.

The most basic form of filtering is when you manually select which columns to keep. You can click the column dropdown arrow and select the columns you want to keep.

If you want to go further, you can use filters that meet certain criteria. The filters provided by Power Query UI are context-dependent because different data types require different filters.

For example, when you click on Text Filters in the above dropdown, you can indicate the text should contain the letter “e” , which would return the below result.

Likewise, on a number column, you can return all values between 150 and 200 by selecting the filter Between… as shown in below image:

That gives us the desired output.

What’s good to remember is that when you click the dropdown in a column header, the menu that appears will change based on the data type of the column. This means that text columns show different options than date or number columns.

The following image illustrates the most common Data Type Filters :

So depending on the types your columns, you the user interface will provide different kind of filters.

Lastly, you can also find filters for columns with the Date Type . The picture below shows a summary of what you can do with Date Filters:

No matter which filter you choose, behind-the-scenes Power Query translates your filters to the corresponding M code. The UI just makes sure that these actions are within reach even to those who don’t know the M-language formulas yet.

Transforming columns is an essential part of working with data in Power Query. It allows you to make changes to the data within a column, like combining columns, splitting a column into separate parts or capitalizing words. For most column transformations you will use the Transform Tab in the ribbon. Here are a few examples of how you can transform a column.

Merge Columns

If you have some columns with related data, you may want to combine columns into a single column. For example, you might have a column for First Name and one for Last Name. You can easily merge columns to retrieve the full name.

To do that:

• Hold CTRL and select both the First Name and Last Name columns.
• Go to the Transform Tab in the ribbon and press Merge Columns .
• Choose a space as a separator and name the column Full Name .
• Now press OK .

By performing these steps, Power Query will concatenate the text values into a single value.

Formatting Text

Sometimes, data may be stored as text, but you need to format it in a different way. With messy data, you could have a mix of uppercase, lowercase or capitalized words. Power Query easily lets you transform the text into the desired form.

To get started:

• Go to the Transform Tab .
• Click on Format and select the desired operation.

You have options to change the capitalization of letters, remove excess spaces and characters or add a prefix/suffix to your words. The below picture shows the effect of lowercasing your text and of capitalizing each word.

With just a few clicks you are able to format your text in the desired way.

Replacing Values

If you have a column with values that need to be changed, you can replace them with new values. For example, you might have a column with sentences that contain the word Horse . You realize you would like to replace the text ‘horse’ with ‘pony’ .

To do this, you can:

• Go to the Transform tab.
• Input both the value to find and the text you want to replace it with .
• Then press OK .

This will perform a replace operation as shown in below image:

Replacing values is a simple and effective way to clean up your data and ensure its accuracy. By following these steps, you can easily replace values in Power Query and create a cleaner, more organized data set.

To learn about more advanced replacing scenarios, you can find step-by-step explanations in the complete guide to replacing values .

Pivoting columns in Power Query is a useful feature that allows you to reorganize your data. When you pivot your data, you take a single column with many values and spread them out over several columns. Here’s how to pivot columns in Power Query:

• Select the column you want to pivot
• Go to the Transform tab
• Select Pivot Columns
• Choose the values column : In the Pivot Columns” pop-up menu, select the values column you want to summarize. Choose the aggregation function you want to use; in most cases, you will use SUM.
• After you’ve selected the values column and the aggregation function, click OK to pivot your data.

This process is illustrated in the below image:

This shows that pivoting is an effective technique to reshape your data.

Aggregating or grouping data in Power Query is another powerful way to summarize and analyze your data. Here are three ways in which it can help:

• Decreases Rows : If you have a lot of rows in your data, grouping can help reduce the number of rows. This can be especially helpful if your data doesn’t fit in Excel.
• Performance Optimizing : If you want to speed up your DAX calculations, you can summarize your data with grouping. This can make your data model run faster.
• Perform Aggregations : Grouping data lets you perform calculations on groups. For example, you can count the number of times an item appears, create a running total , or calculate total sales by group.

So how can you group your data?

• Select the column you want to group by and click Group By on the Transform tab.
• In the pop-up menu, the selected column should appear as Group By column.
• If you want to do multiple calculations, go to the Advanced tab and add one line for each calculation . For example, you can add a line for the amount column and one for the quantity column . Then perform a sum operation.

When you press OK, your data will be summarized by the chosen column, and all underlying values will be aggregated using the sum operation.

If you want to learn more about this topic, check out the article on Grouping Data in Power Query . You can even write custom code to perform additional Group By operations that are not available through the user interface.

In this section we delved into transforming existing columns in a table. So what can you do if you want to create new columns?

Adding columns in Power Query is a useful feature that lets you add new data to your data set using custom logic. You can easily create if statements, concatenate data and create custom formulas. Let’s look at a few examples of how you can use this feature.

Conditional Column

With an if statement you can return a value based on a condition, a so-called conditional column. For instance, you might want to add an if statement that identifies the quantity of a small, medium, or large package.

A low-code way to do this is to:

• Go to the Add Column Tab in the ribbon.
• Click on Conditional Column
• Then, enter an if statement.

An example if statement could be:

Performing this process from start to end then looks as illustrated below:

With just a few clicks you can create a column with your own logic.

Custom Column

In case you need more specific logic and feel comfortable writing code, you can also create a custom column. The custom column allows you to create custom formulas and functions. Imagine you have a table with Countries and Product Prices Excluding VAT. For this table you want to create a price that includes VAT, but each country has a different VAT percentage.

Unfortunately, you can’t create this if statement in a single step using the Conditional Column dialogue box. But if you master creating conditional if statements in Power Query this process is relatively easy.

To do that :

• Go to the Add Column tab and select Custom Column .
• Add the New column name ‘Price Incl VAT’ .
• In the custom column formula box you can include the if-statement .
• Now press OK to review your newly created column.

The below image illustrates how this process works:

Combining Queries in Power Query can be useful when you have data stored in multiple files, sources or queries that you want to combine into a single table. Power Query provides two ways to combine data: Appending Queries and Merging Queries.

Append Queries

Appending Queries is a way to combine data vertically , that is, to stack data tables on top of one another. For example, if you have sales data for different months stored in separate files, you can use the Append Queries function to combine them into a single table.

In the below picture, the Sales March and Sales April data come from different Excel files. They currently exist in different queries. Combine them into a single query you can:

• Go to the Home Tab and select Append Queries .
• Select the other Table from the dropdown menu.

You are now left with a single table that contains sales data from both March and April.

Merge Queries

Merge Queries is a way to combine data horizontally , that is, to add columns from one table to another based on matching values. For those familiar with other approaches, in SQL you would join tables whereas in Excel you can achieve this with VLOOKUP.

For example, if you have sales data in one table and discount data in another, you can use the Merge Queries operation to combine tables into a single table.

To do that you:

• Navigate to the Home Tab
• Click Merge Queries
• Select the table to retrieve new data from
• Determine the Join Kind (in this case Left outer), click OK .

Perform these steps and you end up in the below scenario:

Once this is done, Power Query creates a new column that contains a table object in each cell. To turn this into a column value, you can click the two arrows at the right of the column header, select the desired column and press OK .

And with that, you have seen the entire process of merging tables in Power Query.

Both Appending and Merging Queries may require some data cleaning and transformation to ensure that the data format and types are compatible and match correctly. However, once you have mastered these functions, combining data in Power Query becomes a breeze.

The Advanced Editor is a powerful tool in Power Query that allows you to delve deeper into the M-code generated by your applied steps. The Advanced Editor shows the M language code that Power Query generates based on your transformations.

The Advanced Editor is useful for users who want more flexibility to create transformations that can’t be done through the standard interface. For beginners, it may seem intimidating, but it’s a useful tool to learn as you become more familiar with Power Query Syntax.

To access the Advanced Editor you can click the Advanced Editor button in the Home tab, as illustrated below:

This operation opens a menu that contains the M-code fo your query. An example query could look like the image below:

So what do we see when looking at the advanced editor?

• You will find that the Advanced Editor always opens with a let statement. This indicates that the following lines contain variables.
• Once the variables (applied steps) have been defined, the word in indicates the end of the sequence of variables.
• The step that follows the in at the last line (AddLengthTest) is the variable that Power Query will return.

To ensure that Power Query displays the Applied Steps pane, your query must be formatted with the ‘let..in’ statement correctly. Begin the query with ‘let’ and then list your variables. Conclude with the ‘in’ keyword, followed by the final variable in the series. Referencing a different variable at the end will cause the editor to show only the query result as a single step, instead of the individual query steps.

Overall, the Advanced Editor is a valuable tool in Power Query that allows you to create more complex and precise data transformations. Although it may seem intimidating at first, it’s a tool that can help you gain a better understanding of the Power Query M language and the full capabilities of Power Query.

After you have completed the query and transformed the data in the way that you want, you will need to save your work.

There are two main options available for you to do this:

• Close & Apply: Clicking close and apply loads your queries to the desired destination. This can be an Excel Table, the Power BI data model, a dataflow or any destination connected to your tool. This operation also closes the Power Query Editor.
• Apply : Performs the same action as Close & Apply, except it keeps the power Query window open.

Power Query is a powerful and user-friendly data transformation tool that comes with Excel and Power BI. It offers a variety of built-in functions to connect to different data sources, reshape data, and automate your data transformation.

While there are some limitations to Power Query, including speed and availability issues, it remains a valuable tool to help you transform your data and generate insights that you can use for your business.

In this article, we’ve covered the benefits of Power Query, including its easy learning curve, custom functions, and the ability to reshape data easily. We’ve also looked at the Power Query components, including the Power Query Editor, ribbon tabs, and how to connect to data sources.

Overall, Power Query is a free and accessible tool that can help anyone work with data. And if you’re still not sure, here’s what I think about learning Power Query, even if you know SQL .

Happy Querying!

Recommended Reading…

Power query foundations.

Beginners Guide to Power Query

List Functions (200+ Examples)

Text Functions (150+ Examples)

Creating Tables (40+ Examples)

If Statements in Power Query

Generate the Ultimate Date Table

Advanced Topics

Master List.Generate with Easy Examples

Deep Dive into List.Accumulate

Create Calendar with a Dynamic Language

Replacing Values (Beyond the User Interface)

How to Create Today’s Date in Power Query M

How to Concatenate Values in Power Query (Complete Guide)

Latest from my blog

• Mastering M: Which Power Query Functions to Learn and When
• SWITCH Function (Multiple If Statements) in Power Query
• Perform Join Based on a Date Range in Power Query
• Remove Milliseconds from Time Values in Power Query
• Enumerations in Power Query M

The Definitive Guide to Power Query M

Just wanted to give thanks for your guides. I had to learn Power Query by Trial and Error. I’ve been trying to train others and your guides do an excellent job of simplifying and explaining Power Query.

Thanks! This helps me to explore Power Query perfectly!

Glad to hear that, thanks sweta !

I really appreciate this. Thanks alot for sharing

Amazing and very complete guide. Thanks for sharing Rick.

Happy to hear that Oscar !🙌

It is really great job.Thank for your afford. Hope it will reach so many people and help them.

Thanks Adem

Awesome Rick, great job! Although I’m quite an experienced Power BI specialist and like using PowerQuery very much, it was a pleasure reading your article and I will share it within my network.

Absolutely André, seasoned user or not, there’s always something we can learn in my experience. Glad to read your comment.

Leave a comment Cancel reply

Save my name, email, and website in this browser for the next time I comment.

This site uses Akismet to reduce spam. Learn how your comment data is processed .

About BI Gorilla

BI Gorilla is a blog about DAX, Power Query and Power BI. It was founded in 2018 by Rick de Groot with the goal to provide easy to understand resources to help you advance.

Latest Posts

M Language Function Reference

You can now find everything you need to know on the Power Query M language (syntax, examples, articles etc.) at https://powerquery.how/ .

Free your mind, automate your data cleaning.

©️ 2024 BI Gorilla. Powered by Rocket.net , FlyingPress • Built on theme GeneratePress

Table of Contents

What is power query, how do you enable power query, the four phases of power query, what can you do with power query, different ways to import data to the excel sheet, a demo to get data from csv file, what basic transformations can you perform using power query, combine queries , key pointers, power query in excel: a complete guide.

Reviewed and fact-checked by Sayantoni Das

Power Query makes solving common data tasks easy. A lot of precious time is usually spent on repetitive manual work such as cut & paste tasks or combining columns and applying filters. The Power Query tool makes it a whole lot easier to perform such tasks. 

An added benefit here is that Power Query is easy to use when compared to other BI tools . The Power Query interface is user-friendly. Since it is very similar to the Excel interface, many users will find it comfortable.

Become The Highest-Paid Business Analysis Expert

Power Query is an application for transforming and preparing data. With Power Query you can get data from sources using a graphical interface and apply transformations using a Power Query Editor. Using Power Query, a business intelligence tool offered by Microsoft Excel, you can import data from any number of sources, clean it, transform it, then reshape it according to your needs. In this way, you can set up a query only once, re-use it later by simply refreshing.

As the name suggests, Power Query is the most powerful data automation tool found in Excel 2010 and later. Power Query allows a user to import data into Excel through external sources, such as Text files, CSV files, Web, or Excel workbooks , to list a few. The data can then be cleaned and prepared for our requirements.

Power Query has several useful features embedded in it, such as the appending of data and creating relationships between different data sets. This is called the merging of the data sets. We can also group and summarize data with the help of the tool. Needless to say, it is a very useful tool.

Power Query is available as a free add-in on Excel 2010 and 2013, which you can download from Microsoft's website. The link is available here .

On clicking the Download button, a dialog box opens where you can choose the appropriate download option that suits your OS. Power Query will then be downloaded on your system.

It is a built-in tool starting with Excel 2016 and is available in the Get & Transforms Data Section under Data Tab. 

Power Query allows users to extract, transform, and load (ETL) data from various sources into Excel or Power BI. The four phases of Power Query are:

In this phase, users connect to the data source(s) from which they want to extract data. Power Query supports many data sources, including databases, files, web pages, and more. Users can also specify any required authentication or authorization details during this phase.

2. Transform

Once the data is loaded into Power Query, users can use various data transformation tools to clean, reshape, and transform the data to meet their specific needs. Common data transformation tasks include removing duplicates, filtering data, merging data, splitting columns, and pivoting data.

Power Query also allows users to combine data from multiple sources using various techniques. Users can merge tables, append, or join data using a common key. This phase is beneficial for integrating data from different sources into a single, unified view.

Finally, in the Load phase, users specify where to load the transformed data. They can load the data into an Excel worksheet or a Power BI report or create a connection to the data source so that the data is automatically refreshed whenever the source data changes.

Let’s move forward and understand the concept of Power Query. 

What is a Query List?

A query list refers to a collection or set of queries within a database or data management system. It represents a group of predefined queries that are saved and organized for easy access and execution. A query list typically includes a series of query statements or commands that retrieve, filter, sort, or manipulate data from one or multiple tables or data sources. Query lists provide a convenient way to store and manage frequently used or complex queries, allowing users to quickly execute them without the need to recreate or modify the queries each time. They enhance efficiency, consistency, and reusability in data querying and analysis processes.

Data Preview:

Data Preview in Power Query refers to the visual representation of the imported data within the Power Query Editor window. It allows users to see a sample of the data before applying any transformations, providing a quick overview of the dataset's structure and contents. The Data Preview section displays the first few rows of the imported data, along with the column headers, enabling users to assess the quality and suitability of the data for further analysis or manipulation.

Applied Steps

Applied Steps in Power Query are the sequence of actions or transformations that have been applied to the imported data. Each step represents a specific operation, such as filtering, sorting, renaming columns, or merging queries. The Applied Steps section in the Power Query Editor displays a list of these transformations in the order they were applied. Users can review, modify, or remove individual steps to refine the data preparation process and achieve the desired output. Applied Steps provide transparency and reproducibility, allowing users to track and reproduce data transformations easily.

Formula Bar

The Formula Bar in Power Query is a section within the Power Query Editor that allows users to view and edit the formulas associated with each step or transformation. It provides a text-based representation of the applied transformations, enabling users to manually input or modify the underlying M language code that defines the data transformations. The Formula Bar offers a more advanced and precise way to manipulate data by leveraging the full capabilities of the M language. It is particularly useful for complex or custom transformations that cannot be achieved through the graphical user interface alone.

Power Query is a widely used ETL(Extract, Transform, Load) tool. Let’s look at the three basic steps.

1. Get Data

Importing data is easy with the help of the Get & Transform Data section of the Data tab in Excel. 

You can import data from several different sources.

• From Files: Excel files(Workbook), Text or CSV files, XML files, and JSON files.
• From Databases: SQL Server, Microsoft Access, SQL Server Analysis Services.
• From Other Sources: Excel Tables/ Ranges, Web, Microsoft Query, OData feeds.

2. Transform Data

After importing the data, we can transform it with the help of Power Query. The Power Query Editor helps you transform data based on your needs.

Let’s take a look at the editor and understand its different components.

The Power Query Editor Interface

The six main sections of the Power Query Editor are as follows:

• Query Editor Ribbon:  This ribbon is similar to the one on the Excel interface. Various commands are organized in separate tabs.  
• Query List: This section lets you browse through a list of all queries in your current workbook.
• Formula Bar: The current transformation’s formula will be specified here in the M language.
• Data Preview: You can see the preview of your data based on the current transformation step. You can access various transformation commands by right-clicking on the column header or by clicking on the respective column header's filter option.
• Properties: This section consists of a list of query steps. Here, you will be able to name your query. Naming a query is an important step to identify a query easily.
• Applied steps: Each transformation step you take will be recorded here in chronological order. You can add, remove, edit, or reorder the steps if required.

This was all about the editor interface. Now, let’s proceed by understanding a simple transformation example on the Editor.

Follow the steps below to learn how to sort a table based on a single column.

• First, load the data onto the Editor.
• Then, select the column you want to sort.
• Click on the filter icon, as shown in the image.

• Now, you can sort the data based on Ascending or Descending order from the drop-down menu. 

• On clicking OK, the table gets sorted based on the ‘Name’ column alphabetically.

You will see the M code in the formula bar. This is used to record the steps applied. 

• The applied transformations will reflect in the ‘Applied Steps’ section.

Numerous other transformations can be performed on the Editor. After this step, we need to load the data onto our Excel spreadsheet. 

3. Output to Excel

After performing all the operations on the editor, we will have to output it to our Excel sheet. To do this, click on the Close and Load option on the Ribbon section of the Power Query Editor.

On clicking this option, the Editor closes and loads the result to your worksheet.  

In the next section, we will look at different ways by which we can Import Data to our Excel sheet.

Listed below is a detailed tutorial of importing data from various data sources.

1. Importing Data from a Text File

Follow the steps to import a text file using Power query:

• Click on the Data tab --> Text/CSV File.
• Once we have selected the “Text/CSV file” option, an ‘Import data’ dialog box is opened.
• Select the desired text file and click on Import.
• A dialog box is opened, which shows a preview of the data contained.
• Finally, click on Load to import the data.

2. Importing Data From a CSV File

You can use Power Query to import from CSV files by following the steps below:

• Once we have selected the “Text/CSV file” option, an “Import data” dialog box is opened.
• Select the desired CSV file and click on import.

3. Importing a Single Data Source From an Excel Workbook

To import a Single Data Source, follow these steps:

• Click on the Data tab --> Get Data command. This opens up a drop-down menu. The drop-down menu offers different options for us to import our data. To import from the Excel workbook, we select the option ‘From File’ and then ‘From Workbook’.
• Excel opens up a dialog box that helps us navigate and select the workbook.
• Once we have navigated to the workbook location, we can click on it and then click ‘Open’.
• This opens up the navigation dialog box. The navigation dialog box gives you a set of data sources.
• From here, we can select the data on which we want to work.
• Finally, click on ‘Load’ to import the data.

4. Importing a Multiple Data Source From an Excel Workbook

The following steps will help you import multiple data source from the Excel workbook:

• Click on the Data tab, followed by Get data command. On clicking this, a drop-down menu opens up. The drop-down menu offers different options for us to import our data. To import from an Excel workbook, we select the option ‘From File’ and then ‘From Workbook’.
• In the navigation dialog box, there is an option to ‘Select Multiple Items’. Upon selecting this option, we can choose more than one item.
• From here, we can select the multiple data sources on which we want to work.

So, these were a few techniques by which you can import data to Excel. Going ahead, let’s look at a simple demo on how you can Import Data from a CSV file.

We will be explaining how to import data from a CSV file. This process is simple and consists of a few steps.

Importing Data From a CSV File

• Click on the Data tab, followed by which a Text/CSV file command is found.
• Select the desired CSV file and click on import. 

• A dialog box named after the CSV file is opened. It shows a preview of the data contained.

As you can notice, 14 rows are loaded onto the Excel sheet.

Now, let’s move forward and understand various tasks and transformations that can be performed using Power Query. 

In this section, let’s look at various transformation functions that can be performed easily with the help of a few mouse clicks.

1. Text Formatting Functions

In this section, you will learn how to format text in Uppercase, Lowercase, and understand how to use the Trim operation.

Step 1: Load the required data onto the Power Query Editor. This can be done by selecting the respective data source from the Get & Transform Data section of the Excel Data tab. This will open up the Editor, which allows us to edit the data.

Step 2: Click on the column name and then go to the “Transform” tab, which will display a variety of options. Clicking on the option to Format text will open up a drop-down menu with a text edit option of ‘UPPERCASE’.

Step 3: Finally, on selecting the UPPERCASE edit option, all the text in the given column will be converted to uppercase.

Step 1: Load the required data onto the Power Query Editor. This can be done by selecting the respective data source from the Get & Transform Data section of the Excel data tab. This will open up the Editor, which allows us to edit the data.

Step 2: Click on the column name and then go to the ‘Transform’ tab, which will display a variety of options. Clicking on the option to Format text will open up a drop-down menu with a text edit option of ‘LOWERCASE’.

Step 3: As you can see, all the text from the selected column will be converted to lowercase.

Step 1: Load the required data onto the Power Query Editor. This can be done by selecting the respective data source from the Get & Transform Data section of the Excel data tab. This will open up the Power Query Editor, which allows us to edit the data.

Step 2:To remove all the extra white spaces from the data, click on the column name, and then select the ‘Transform’ tab, displaying various options. Clicking on the ‘Format’ option will display a drop-down menu with a text edit option called ‘Trim’.

Step 3: Finally, on selecting the Trim edit option, all the extra white spaces in the given column will be removed.

Strategies for Leadership Excellence

2. Splitting a Column Using Delimiters

Step 2: To split the column with the help of a delimiter from the data, click on the ‘Transform’ tab followed by the ‘Split column’ option. This will display a drop-down menu with an option to split the data By Delimiter.

Step 3: A dialog box appears where you can select a delimiter. Then click on OK.

Step 4: Now, we can see that the data is split into two columns concerning the delimiter.

3. Transpose a Data Table

Step 2: Since we want to rotate the rows to columns, we have to navigate to the Transform tab. Upon selecting, it will show us an option to Transpose the data.

Step 3: On clicking the transpose option, the rows will be converted to columns. To load the changes into a new worksheet, go to the Home tab and click on ‘Close and load’.

4. Removing Duplicates Using Power Query

Look at the duplicate data highlighted in the image below.

Step 2: Now, we need to navigate to the Home tab → Remove rows option, which will open up a drop-down menu. Click on the ‘Remove Duplicates’ option. 

Step 3:  As you can notice, the data is now free from duplicates. To save the updated table without duplicate rows, go to the Home tab and click on ‘Close and Load’. 

Power Query has two different options that help us combine different datasets. The two options are:

In Power Query, the append operation creates a new table by joining all the rows from the first query, followed by all rows from the second query. Follow the steps below to understand how to perform an Append operation.

•  Firstly, we have to load the data into the Excel workbook. In this demonstration, you will learn how to Append data from a CSV file.
• This can be done by selecting the Data tab, followed by the ‘Text/CSV File’ command. 
• Once we have selected the option, an Import Data dialog box opens. Select the desired CSV file and click on import.
•  A dialog box opens, which shows a preview of the data contained. Clicking on ‘Load’ will enter the data in a new sheet. 
• Continue this step to add the required data into new sheets.

Step 2: Now, to append the data available on different sheets, we can navigate to the Data Tab. Here we can find an option called Get data, clicking on which will open up a drop-down menu. You will find an option called Combine Queries. On selecting it, you will find the ‘Append’ option.

Step 3: Clicking on ‘Append’ will open up a window with different options where we can choose to append two tables or more than three. Next, we have to select the sheets that have to be appended. When done, we can click on OK.

Step 4: The Power Query editor opens up, and the data has now been appended. We can click on ‘Close and Load’ to save these changes, which loads the updated data to an Excel spreadsheet.

The Merge option is similar to the JOIN function in SQL. Merge is a way of combining two existing queries and creating a new query. 

• Firstly, we have to load the data into the Excel workbook. In this example, we will demonstrate how to Merge data from a CSV file.
• This can be done by clicking on the Data tab, followed by the ‘Text/CSV File’ command. 
• Once we have selected the option, an Import Data dialog box opens. 
• A dialog box opens, which shows a preview of the data contained. Clicking on ‘Load’ will enter the data in a new sheet. 
• Continue this step to add all the required datasets to be merged into different sheets.

Step 2: Now, to Merge the data available in different sheets, we have to navigate to the Data tab. Here we can find an option called Get Data. On clicking it, a drop-down menu will be displayed, which has the option to Combine Queries. On selecting this, click on ‘Merge’.

Step 3: A window will be displayed where we can select the sheets that we want to merge. Now, choose the two columns by clicking on the column header based on which we want to connect both the sheets. Then, click on OK.

Step 4: Once that is done, the Power Query editor opens up with a new column in the end that holds the merge result. To save the changes made, we click on “Close and Load”.

Step 5: The merged data is now loaded onto our Excel worksheet. 

This was all about combining the queries using Merge and Append operations. 

The Queries & Connections Window:

The Queries & Connections window is a central hub in Power Query where you manage and interact with your data queries and connections. It provides a clear overview of all the queries in your workbook, allowing you to navigate, edit, and refresh them easily. Additionally, the window displays the connections to external data sources, enabling you to modify connection properties, update credentials, or create new connections. The Queries & Connections window is a powerful tool for organizing and controlling your data connections and queries in Excel.

Difference Between the Transform and Add Column Tabs:

In Power Query, the Transform and Add Column tabs are two key sections that facilitate data manipulation. The Transform tab offers a wide range of data transformation options, allowing you to perform actions such as filtering rows, sorting data, removing duplicates, and splitting columns. On the other hand, the Add Column tab focuses specifically on adding new calculated columns to your data. It provides access to various functions, operators, and formulas that can be used to create custom columns based on existing data. Understanding the distinction between these two tabs helps you effectively manipulate and enhance your data in Power Query.

The Transform Tab:

The Transform tab in Power Query houses an array of data transformation features. It enables you to perform essential operations like changing data types, replacing values, aggregating data, pivoting columns, and merging queries. This tab also offers advanced transformation capabilities, such as applying conditional logic, invoking custom functions, unpivoting data, and splitting columns based on delimiters. The Transform tab empowers you to cleanse and reshape your data, ensuring it is in the desired format for further analysis.

The File Tab:

The File tab in Power Query provides options for managing queries and connections at the workbook level. From this tab, you can create new queries, import data from external sources, export queries to other workbooks, or load queries from a query repository. Additionally, the File tab allows you to access the Options menu, where you can configure various Power Query settings, such as privacy levels, global query settings, and regional preferences. The File tab serves as a gateway to essential file and configuration-related functionalities in Power Query.

The Home Tab:

The Home tab in Power Query is the central hub for common data manipulation tasks. It offers a set of frequently used operations, including filtering, sorting, grouping, removing columns, and transforming data types. This tab also provides options to manage query settings, such as refreshing queries, viewing query dependencies, and accessing query properties. The Home tab serves as a convenient starting point for most data transformation activities, allowing you to quickly perform common tasks and navigate to other sections of Power Query.

The Add Column Tab:

The Add Column tab in Power Query is dedicated to enhancing your data by creating new calculated columns. It offers a variety of functions, operators, and formulas that you can use to define custom calculations based on existing data. From basic arithmetic operations to advanced text manipulation, date calculations, and conditional logic, the Add Column tab provides a wide range of tools to transform your data and derive additional insights. This tab enables you to extend the capabilities of your data model and tailor it to your specific analysis requirements.

The View Tab:

The View tab in Power Query provides options to customize the visual appearance and layout of the Power Query Editor window. It allows you to change the zoom level, toggle the Formula Bar display, show or hide gridlines, and adjust the column widths. The View tab also offers options to enable or disable the Formula Bar autocomplete feature and configure the display of formula errors. These customization options help improve the overall user experience and make working with Power Query more efficient.

How to Load Data Back to the Worksheet:

After performing the necessary data transformations in Power Query, you can easily load the data back to the worksheet in Excel. In the Power Query Editor window, you have the option to choose how you want to load the data. You can either load the data to a new worksheet or append it to an existing worksheet. Additionally, you can specify the destination range for the loaded data. By selecting the appropriate options, you can seamlessly transfer the transformed data from Power Query back to the worksheet, where you can further analyze and visualize it using Excel's powerful tools and features.

Auto Refresh a Query:

Power Query provides the functionality to automate the refresh of data queries. This feature is especially useful when dealing with dynamic data sources or when you need to keep your data up to date without manual intervention. To enable auto-refresh, you can specify the refresh settings for a query in the Query Properties window. You can choose to refresh the query upon opening the workbook, at regular intervals, or based on specific triggers like workbook changes or data source availability. By setting up auto-refresh, you ensure that your data is always current, eliminating the need to manually refresh the queries each time.

Export Connections:

In Power Query, you have the ability to export connections, which allows you to share or reuse them across multiple workbooks. Exporting connections enables you to package the connection details, including the data source, authentication credentials, and connection settings, into a file that can be imported into another workbook. This feature is particularly valuable when you want to replicate the same connections in different workbooks or when you need to share connections with colleagues or clients. By exporting connections, you simplify the process of establishing data connections and ensure consistency across your data analysis projects.

In this article, you have learned how to load data using Power Query, perform transformations, and output the data back to your Excel worksheet. Using the Power Query tool, you are saving loads of time by performing numerous functions just with the help of a few clicks! 

Whether you are interested in learning the basics of Excel or want to develop more advanced Excel skills, Simplilearn has a Post Graduate Program in Business Analytics Course for you.

If you have any questions for us, please feel free to mention them in the comments section of this Power Query article, and we’ll have our experts answer it for you right away.

1. What is an Excel Power Query?

Excel Power Query is a data transformation and preparation tool developed by Microsoft. It allows users to extract, transform, and load data from various sources into Excel or Power BI using a visual interface. It is a powerful tool for data preparation and analysis tasks.

2. How do you get Power Query in Excel?

Power Query is built into Excel 2016 and later versions but may need to be activated sometimes. To activate it, users can go to the "File" menu, select "Options," and then choose "Add-ins." They can then select "COM Add-ins" and enable "Microsoft Power Query for Excel."

3. Is Power Query free with Excel?

Yes, Power Query is a free add-in for Excel 2016 and later versions and is also available as a built-in feature in Excel for Microsoft 365. It can be downloaded and installed for free on older versions of Excel.

4. What are the basics of Power Query?

The basics of Power Query involve importing, transforming, and combining data from various sources in Excel, allowing users to clean, reshape, and analyze data without complex formulas.

5. What is Power Query Excel used for?

Power Query in Excel is used for importing, transforming, and cleaning data from multiple sources, enabling users to perform advanced data analysis and create unified views of their data.

6. How do you create a Power Query in Excel?

To create a Power Query in Excel, go to the Data tab, click on the Get Data button, select the data source, specify import options, apply transformations in the Power Query Editor, and load the data back to the worksheet.

7. What are the benefits of Power Query?

The benefits of Power Query include streamlined data preparation, automated data refresh, easy data transformation, support for various data sources, improved data analysis capabilities, and the ability to repeat and modify data transformations with a single click.

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Recommended Reads

Introduction to Microsoft Azure Basics: A Beginner’s Guide

Learning the Concept of Select Query in PHP

What is Power BI?: Architecture, and Features Explained

Getting Started with Microsoft Azure

Power BI Vs Tableau: Difference and Comparison

Get Affiliated Certifications with Live Class programs

Post graduate program in business analytics, business analyst.

• Industry-recognized certifications from IBM and Simplilearn
• Masterclasses from IBM experts

Post Graduate Program in Business Analysis

• Certificate from Simplilearn in collaboration with Purdue University
• Become eligible to be part of the Purdue University Alumni Association
• PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.

25 Amazing Power Query Tips and Tricks

Power query is amazing data transformation tool! It allows you to import and transform data with ease and helps to create repeatable and robust procedures for your data.

It’s going to save you time and effort if you put in the small amount of time to learn it.

The best part is, it’s built right into Excel 2016 or later. It’s also the same technology that’s used in Power BI, so you’re learning two in demand data skills at the same time!

If you haven’t heard of power query and the awesome things it can do, or you want to fully understand it better, then check out my Complete Guide to Power Query beforehand.

Here are some tips and tricks to help you get the most out of this incredibly useful and easy tool.

Video Tutorial

Delete steps until end.

Sometimes when you are building a query with a lot of steps, you end up going down the wrong path. If you’re able to pin point at which step your query started going wrong, then you can delete that step and all steps after to start over (without fully starting over).

In the Applied Steps window pane, right click on the first step you want to delete and then select Delete Until End from the menu.

This will delete that step and all query steps after that step.

Preview or Navigate to Table Objects

A column in your query might contain table objects. If this is the case, the column’s row entries will display the text Table and the data type icon.

You can either preview the table or navigate to it in the query depending on where you click in the cell.

If you click on the blank area of a cell containing a table object, then you will be shown a preview of the table below your query.

When you hover the mouse cursor over the word Table in the cell, it will change to a pointing hand icon. When you click on this part the query, it will add a navigation step to the query which navigates to that particular table.

Double Click to Edit a Query

Editing a query can be done a couple different ways from the Queries & Connections window pane.

• You can right click on the query then choose Edit from the menu.
• You can hover the cursor over the query until the peek window appears, then press the Edit button from the peek window.

The quickest way is to double left click on the query. This will open the query editor on the selected query.

Automatically Add a Row Index to any Query Loaded to a Table

You can add an index column to any query through the power query editor, but it is also possible to add an index row to a query that’s been loaded to a table in a special way. Select a cell in the table output and go to the Data tab and press the Properties button.

Note that the Properties option found in the right click menu of the Queries & Connection pane will open the Query Properties window and not the External Data Properties window that is needed for this tip.

This will open the External Data Properties menu and you can check the Include row numbers option and press the Ok button.

Now the next time you refresh the query loaded to your table, a new column called _RowNum will appear as the left most column and will contain an index for the row number starting at 0.

Change the Default Load Options

When you press the Close & Load button for a query the first time, power query will load the data into an Excel table in the workbook and doesn’t add the data into the data moedel. This is the default load settings in power query.

If you want load the data as a connection only or need to load it into the data model to use with power pivot later on, then you need to select Close & Load To instead and select these options each time you create a new query.

If you find most of the queries you end up creating need to be a connection only or need to load into the data model, then you can change the default load settings. Go to the Data tab then choose Get Data then open the Query Options .

In the Global Data Load settings, choose the Specify custom default load settings option and then select or deselect the desired options.

• Unchecking both the Load to worksheet box and the Load to Data Model box will result in your queries loading as connection only and will not load the data into the data model.
• Checking the Load to worksheet box and unchecking the Load to Data Model box will load queries into an Excel table and not load data to the data model. Note this is the same as when Use standard load settings is selected.
• Unchecking the Load to worksheet box and checking the Load to Data Model box will result in your queries loading as connection only and will load the data into the data model.
• Checking both the Load to worksheet box and the Load to Data Model box will load queries into an Excel table and will load the data into the data model.

Display a Monospaced Font in the Query Editor

It can sometimes be hard to tell if each item in a column of data has the same character length. Not all characters have the same width when using the default query editor font. This can make it hard to tell if you will be able to split a field by character length consistently for the entire column.

Change the font to a monospaced character set by going to the View tab and checking the Monospaced option. This will make it easy to see if things line up in character count.

Navigate Columns with the Arrow Keys

You can select a column by clicking on its column heading. The column will turn to a light green colour when selected and you will then be able to perform various transformations on it by either right clicking on the column heading or using any of the transformation commands from the ribbon.

Once a column is selected, you can navigate to other columns using the left or right arrow keys. In fact, you don’t even need to select a column first, just press the right arrow key and the first column will be selected.

The same trick can be done with rows of data. Once a row is selected from clicking on the row heading you can navigate to other rows using the up or down arrow keys. Again, no need to select a row first, just press the down arrow key and the first row will be selected.

If you select a single cell in a column, you can use the Ctrl + Space keyboard shortcut to select the entire column. You can also use the Ctrl + A keyboard shortcut to select the entire table. Just like in a regular Excel worksheet!

Comments in Power Query Formula Language

You can do a lot with power query just from the UI without ever touching any of the M code that is created behind the scenes. If you do start exploring the M code using the advanced editor ( View tab then Advanced Editor ), then you’re likely going to want to add comments to your code.

You can do this in two ways.

You can create a single line comment by starting the line with a // double forward slash.

You can create multiple line comments by starting the first line of the comment with a /* forward slash followed by an astrix, then ending the last line of the comment with a */ astrix followed by a forward slash.

Unfortunately, any comments you add into the advanced editor will only be visible in the advanced editor. You won’t be able to see them in the formula bar when navigating through the applied steps of your query.

Add Comments Without Opening the Advanced Editor

It is possible to add comments without opening the advanced editor. Select the step from the applied steps to which you want to add a comment. Add your comment to the end of the step using either comment type and press the Enter button.

The comment will appear visible in the formula bar until you navigate away to a different step or different query. When you come back it won’t be visible in the formula bar any more, but it will still be there in the M code and visible in the advanced editor.

Add Comments that Stay Visible in the Formula Bar

I already said comments are only visible from the advanced editor, but there is a hack to get them to show up in the formula bar.

Write your comment inside a formula instead of at the end of the step. You’ll need to use the opening /* and closing */ comment characters as the comment will need to be closed so the rest of the formula is not considered as part of the comment.

This will remain visible in the formula bar when navigating through the applied steps of your query.

Add Comments with the Query Step Properties

You can add comments to any of the query steps listed in the applied steps window pane using the query’s properties window. Right click on the query and select Properties from the menu.

You’ll be able to add a description to the step. This description will appear as a single line comment above the M code for the step in the advanced editor.

You can also change the name of the step from this window. This will change the references for the step in the advanced editor as well.

Create a Sequential List

Creating a sequential list object in power query is easy when you know how. Add a custom column with the following formula for a list starting at 5 and ending at 10.

This will even work by referencing columns instead of static hardcoded numbers. Use a formula similar to the following where [Start] and [End] are two whole number columns in your query.

={[Start]..[End]}

If either the Start or End column is a decimal number, the expression will result in a error.

If the Start number is greater than the End number, the expression will result in an empty list.

Avoid Case Sensitivity with Filters

Power query is case sensitive. This can cause errors in your data analysis down the road if you’re not careful.

If you filter out the item Keyboard from your data and later it shows up as keyboard in the source data, then your query won’t filter out the lower case version.

This can be avoided by transforming a column to upper case before applying any filters.

We can also retain the original case in our data by using the UPPERCASE transformation found in the Add Column tab. This will create a new column with the upper case values while keeping the original column intact.

Now we can apply any filter on the uppercase version of the column and then delete the column after. This allows us to apply a case insensitive filter without altering the case in our data.

Double Click to Rename a Column

You’re probably going to be renaming a lot of columns during your power query career so you’re going to want to learn the quickest way to do it. A double left click on the column heading will allow you to quickly change a column’s name.

Rename a Column with a Keyboard Shortcut

You can rename a column using a keyboard shortcut. This one is a bit slower, but worth knowing for those that prefer the keyboard to the mouse. With the column selected, press F2 on the keyboard and you can rename the column heading.

Navigate Through Complex Query Dependencies

This feature can be a life saver when your workbook starts to get overloaded with queries. It can be easy to forget what query is using what data source and which queries depend on each other.

Go to the View tab and press the Query Dependencies command to open the query dependencies viewer. This will give you a visual representation of all the queries in the workbook along with their precedents and dependents.

There’s a lot going on in the Query Dependencies window.

• You’ll be able to see your data sources. These are the smaller boxes in the window and will have a larger icon indicating the type of source data.
• Left click on any item and it will turn a light green along with all its dependents and precedents.
• Right click on any item and a menu will appear with the option to center the view on that item or center the view on all the precedent and dependent items.
• Left click and drag any empty area to move the view around.
• You can change the layout to show dependencies from top to bottom, bottom to top, left to right or right to left.
• You can zoom in or out on the view manually using the zoom bar. Another way you can zoom in on the view is using the mouse scroll wheel if yours is equipped with one. Another option is to double left click on any empty area to zoom in.
• You can fully zoom out on the view using the zoom out button on the lower right.

Drill Down to a Single Value

Drill down to any single item in your query. Right click on the cell and choose Drill Down .

Depending on your data, this could be a single value, list or a table. This can be useful for creating a single value from your data which you can reference in a custom column calculation.

Enable Fast Data Load in Power Query

Faster is always better, right? If your queries are slow, you can enable the fast data load option to speed them up. Go to the Data tab and press the Get Data button, then open the Query Options .

Go to the Global Data Load settings and check the Fast Data Load box. Your queries will take less time to load, but Excel may become unresponsive during the loading.

Disable the Auto Detect Data Type Feature

When you import data into Excel with power query, Excel will guess what data type each column is based on the first few hundred rows. Power query will then automatically create query steps to promote the first row to column headings (for CSV files) and change data types.

If you prefer to control the data type selection and not have excel automatically create this steps for you, then you can disable this option.

To open the query options, go to the Data tab then Get Data then choose the Query Options .

Go to the Current Workbook Data Load settings and check the box to Automatically detect column types and headers for unstructured sources .

Next time you create a query, you won’t see any steps other than the source step.

Explore Power Query’s Function Library

Power query’s M code language is a functional language. This means the M code is mostly built with functions that do specific things based on their inputs. Writing M code in power query is like building with Lego, there are many different types of Lego pieces and each piece is designed for a specific purpose.

M code comes with a large selections of functions to use. In fact you can explore the entire library of available functions from within the power query editor.

Create a new blank query, go to the Data tab and press the Get Data command then choose From Other Sources then choose Blank Query . Now in the formula bar enter =#shared and press Enter .

Power query will then show all the items available to the workbook. This will include all the power query functions!

If you click on any of the functions listed, you’ll be taken to a mini help guide for that function.

• The function name is shown.
• There is a brief description of what the function does.
• You can test out the function on your data.
• The functions syntax is shown.
• There is a simple example shown to demonstrate the input and output of the function.

Split a Query into Two Parts

Maybe your query is getting too long, or maybe you want to use the first part of it as a source in other queries. If this is the case, you can split up a query into two parts.

Right click on the step on which you want to divide the query then choose Extract Previous from the menu. This will extract the steps before the selected step into a new query. You’ll then be prompted to name the new query.

The remaining steps will then reference the new query as its source.

Copy and Paste Queries to a New Workbook

If you need to reuse a power query from a previous workbook, you can easily copy and paste it from the old workbook to the new workbook.

In the Queries & Connections window of the old workbook, select the queries you want to copy then right click and choose Copy from the menu. Now navigate to the new workbook and right click in the Queries & Connections window and choose Paste from the menu.

This will copy all the selected queries along with all the queries they reference.

If any of the copied queries, or the queries they depend on, reference a table in the old workbook then those queries will show a Download did not complete error message. You will need to copy over those tables into the new workbook separately to fix this.

Double Click to Rename a Query

You can quickly rename any query from the query list pane on the left hand side of the query editor. Double left click on the query which you want to rename, then type in the new name and press Enter to confirm the change.

Double Click to Hide or Show the Query Editor Ribbon

If you find the ribbon in the power query editor is getting in the way, you can hide. Double left click on any of the ribbon tabs to hide the ribbon. Double left click on the tab again to show the ribbon.

Yep, this is the same trick from the regular workbook ribbon but it can also be used in the power query editor!

Disable Relationship Detection

This is another tip that can potentially speed up your power query queries.

When you load data into the data model from power query, Excel will try to find and build relationships between the new data and other tables in the data model. This extra processing step will slow down your queries.

If you are always loading your data into the data model then you might want to turn this feature off to save time. Go to the Data tab then press the Get Data button and open the Query Options .

In the Current Workbook Data Load settings, uncheck the Create relationships between tables when adding to the Data Model for the first time option. This will prevent Excel from creating the relationships between tables in the data model.

About the Author

John MacDougall

Subscribe for awesome Microsoft Excel videos 😃

I’m John , and my goal is to help you Excel!

You’ll find a ton of awesome tips , tricks , tutorials , and templates here to help you save time and effort in your work.

• Pivot Table Tips and Tricks You Need to Know
• Everything You Need to Know About Excel Tables
• The Complete Guide to Power Query
• Introduction To Power Query M Code
• The Complete List of Keyboard Shortcuts in Microsoft Excel
• The Complete List of VBA Keyboard Shortcuts in Microsoft Excel

Related Posts

5 Ways to Get the Current Date or Time in Excel

Apr 14, 2020

Learn 5 different ways to add the current date or time into Excel with keyboard shortcuts, functions, power query, power pivot and power automate.

5 Ways To Change Text Case In Excel

Apr 30, 2019

Learn how to change text case between lower, upper and proper case in Excel using Excel functions, flash fill, power query, DAX and power pivot.

37 Awesome Excel Mouse Tips & Tricks You Should Know

Apr 15, 2019

While the keyboard is generally quicker, you shouldn’t completely ignore the mouse. There are also some great time saving mouse shortcuts as well. In this post we’ll take a look at some of the best Excel mouse time saving tips and tricks.

19 Comments

Thanks for the article .. that was helpful …

Looks great!

Extremely well put together – a great help for those exploring the possibilities of Power Query! Thank you.

No problem Roland!

+1 @Roland w.r.t. comments in Advanced Editor: if a comment line is immediately above a step it will turn into a tooltip when you hover over the step name in the “Applied Steps” pane.

Also the query code itself is UNICODE so you can use symbols in step names. Usually this is overkill, but you can use it to draw attention to things like debugging steps.

Great tips Steve, thanks!

Hi John, Thanks for tips. How does one change a query that has already been loaded but realise there afterwards that it only needs to be a connection? I can only think of duplicate / edit – then change the connection and delete the original. Have yet to try that as I have many to do (to reduce the extra sheets not needed…)

Just delete the table it loaded to from the workbook. The query will become a connection only.

You can also change things by right clicking on the query in the query and connections window pane and selecting load to from the menu.

Thank You Very Good

Great tips, thanks.

No problem Martin!

Great material!, thanks a lot

Hi John, I am loading a power query to a table and then adding additional columns to the table to perform calculations. When I refresh the query, it takes a significant time because it is calculating during the background refresh. I turned off Automatic Calculations but it continues to calculate. I would like it to refresh the table from the power query and then calculate the columns I have added. Any ideas? Thanks

The only thing I can think of is to delete all the formulas, except for those in the first row before refreshing. Then copy down the formulas from the first row after refresh.

Are you aware of a way (or any third party editor or plugin) that allows you to open the Power Query Advanced Editor for a given Excel file without all the clicks? We have found Power Query really useful, but cumbersome to work with given that it takes about 5 clicks to get to the Advanced Editor, which is generally where we want to be.

Sorry, not aware of anything. You can copy queries from the queries and connection pane into a text editor and this will give you the M code.

Thanks – it feels like something Microsoft needs to solve if they want better adoption of this technology. Would be great if you could open an Excel file with Visual Studio and edit the embedded M code in an editor that has syntax highlighting, Intellisense, etc. It’s surprising to me that nothing like that exists.

I did find one useful shortcut that is worth sharing. You can reduce the number of clicks to get to the editor if you add the “Launch Power Query Editor” command it to the Quick Access Toolbar. Helps alleviate some pain at least…

Hello-we are using one of these at work but the folks using it are asking if there is a way to add a column that they can put notes in for each row that will stay with that row as it refreshes. For us, each row is a work order and it would help immensely with work planning. If it cant-are you aware of any other way to pull and refresh data from a source that does allow for a comment column?

Get the Latest Microsoft Excel Tips

Follow us to stay up to date with the latest in Microsoft Excel!

Microsoft Excel - Intro to Power Query, Power Pivot & DAX

Get Started (0 of 92 lessons completed)

Description

This course introduces Microsoft Excel's powerful data modeling and business intelligence tools: Power Query, Power Pivot, and Data Analysis Expressions (DAX). If you're looking to become a power Excel user and absolutely supercharge your analytics, this course is the A-Z guide that you're looking for.

We'll kick things off by introducing the “Power Excel” landscape, and explore what these tools are all about and why they are changing the world of self-service business intelligence.

Using sample data from a fictional supermarket chain, we'll get hands-on with Power Query; a tool to extract, transform, and load data from flat files, folders, databases, API services and more. We'll practice shaping, blending and exploring our project files, and create completely automated loading procedures with only a few clicks.

From there we'll dive into Data Modeling 101, and cover the fundamentals of database design and normalization (including table relationships, cardinality, hierarchies and more). We'll take a tour through Excel's data model interface, introduce some best practices and pro tips, and then create our own relational database to analyze throughout the course.

Next, we'll use Power Pivot and DAX to explore and analyze our data model. Unlike traditional pivots, Power Pivot allows you to analyze hundreds of millions of rows across multiple data tables, and create supercharged calculated fields using a formula language called Data Analysis Expressions (or “DAX” for short). We'll cover basic DAX syntax, then introduce some of the most powerful and commonly-used functions — CALCULATE, FILTER, SUMX and more.

If you're ready to take your Excel game to new heights and join the leading edge of analytics & business intelligence, this course is for you. It's time to stop fighting with tedious, manual tasks and struggling with “old-school” Excel; join me on this journey and emerge a certified NINJA.

See you in there!

Course Content

• My presentations

Auth with social network:

Download presentation

We think you have liked this presentation. If you wish to download it, please recommend it to your friends in any social system. Share buttons are a little bit lower. Thank you!

Presentation is loading. Please wait.

Introduction to power query

Published by Παρθενιά Παπανικολάου Modified over 5 years ago

Similar presentations

Presentation on theme: "Introduction to power query"— Presentation transcript:

Learningcomputer.com. Using this Tab, you can import data from external sources including but not limited to: Text files Microsoft Access databases Web.

WEB DESIGN TABLES, PAGE LAYOUT AND FORMS. Page Layout Page Layout is an important part of web design Why do you think your page layout is important?

The State of SharePoint BI

KompoZer. This is what KompoZer will look like with a blank document open. As you can see, there are a lot of icons for beginning users. But don't be.

1 Computing for Todays Lecture 22 Yumei Huo Fall 2006.

Database Updates Made Easy In WebFocus Using SQL And HTML Painter Sept 2011 Lender Processing Services 1.

Importing Data Text Data Parsing Scrubbing Data June 21, 2012.

ACOT Intro/Copyright Succeeding in Business with Microsoft Excel

Analysing Data with Excel Importing Data from a Text File To import data from a text file: 1.Start Excel. 2.Click File, click New, click Workbook,

Miscellaneous Excel Combining Excel and Access. – Importing, exporting and linking Parsing and manipulating data. 1.

…and how to do stuff with them Copyright © The University of Southampton 2011 This work is licensed under the Creative Commons Attribution License See.

Damian Tamayo Tutorial DTM Data Generator Fall 2008 CIS 764.

Microsoft ® Office Excel 2003 Training Using XML in Excel SynAppSys Educational Services presents:

1 Data Manipulation (with SQL) HRP223 – 2010 October 13, 2010 Copyright © Leland Stanford Junior University. All rights reserved. Warning: This.

Highline Class, BI 348 Basic Business Analytics using Excel Import, Clean, Transform, Data 1.

Advanced Tips And Tricks For Power Query

1 Data Manipulation (with SQL) HRP223 – 2009 October 12, 2009 Copyright © Leland Stanford Junior University. All rights reserved. Warning: This.

MICROSOFT ACCESS – CHAPTER 5 MICROSOFT ACCESS – CHAPTER 6 MICROSOFT ACCESS – CHAPTER 7 Sravanthi Lakkimsety Mar 14,2016.

Eugene Meidinger Execution Plans

The power of Power Pivot Cristian Nicola DynamicsBIGuide.com.

About project

© 2024 SlidePlayer.com Inc. All rights reserved.

Power Query: the 2024 beginners’ guide

• Efficiency Tips , How to guides , Power BI

Ever wondered what the Microsoft Office add-in Power Query is and how it works? If so, you have come to the right place.

We spoke to Pauline Jouffret, our Power Query expert at UpSlide, for all her top tips to help you master the basics of this data transformation and preparation tool.

Power Query, in Microsoft 365, empowers users to transform data easily without IT assistance, combining sources, cleaning, and reshaping data without coding.

It acts as an ETL tool, letting you work with vast data sources without changing the original, thus keeping Excel files lightweight and efficient.

Let’s identify the 4 key elements of the Power Query Editor: the main ribbon, queries, data table preview and query settings.

What is Power Query?

Power Query is a user-friendly, powerful  data transformation and preparation tool within Microsoft 365 . Because of its low-code interface, Power Query is an excellent tool to combine and transform data without the help of your IT department. It gives power and flexibility to end-users, saving you money and resources.

You’ll want to use Power Query when: 

• Processing large amounts of data
• Importing data from different sources (e.g. Excel, CSV, SharePoint, SQL, and Salesforce)
• Cleaning, transforming and combining data according to your needs without coding
• Repeating regular data transformations, for example, in a monthly report

A key benefit of Power Query is that it doesn’t change the original data source. The outcome is like a snapshot of reshaped data brought to Excel. Since the source data is not directly imported, it does not make your Excel file heavier (i.e. it doesn’t slow down your computer!)

In summary, Power Query is an ETL  (Extract-Transform-Load) tool that everyone can use because no coding skills are required.

Power Query is the perfect solution if you don’t have a data engineer within your organization. The low-code interface means that anyone can transform data with very minimal training.

Pauline Jouffret

Data Engineer

What is the difference between Power Query and Power BI?

Power Query is part of Power BI. It’s the first step of a BI project where you want to ETL your data, calculate KPIs and present them in charts.

The interface of the  Power BI Desktop software allows you to benefit from three technologies (Power Query, Power Pivot and Power View ). With Power BI, you can do all the steps of the data analysis chain, from extracting data to creating visuals on an  interactive dashboard  that you can share and analyze.

> Discover UpSlide’s Power BI to PowerPoint or Word Link

Is Power Query a good alternative to Excel VBA?

Power Query can easily replace VBA ( Visual Basic for Applications ) as it enables you to:

• Process your tables simply by clicking on buttons. Plus, no coding skills are needed!
• Visualize your operations step-by-step without running a single macro.
• Easily edit the order of steps by dragging and dropping! Plus, you can add and delete unnecessary steps with a simple click.

However, Power Query can’t replace VBA when it comes to interacting with Excel’s interface, automating processes, generating new files, and sending emails. 

Is Microsoft Power Query free?

Yes. Power Query is a free extension of Excel and Power BI. Within Power BI, Power Query can also be called Power Query Editor. Though Power BI desktop is free to download, you will need to pay for a license if you want to share any dashboards with your colleagues.

Is there a Power Query for Mac?

Yes. Power Query is also  compatible with Excel for Mac. If you’re a Microsoft 365 subscriber, the Power Query experience is available in Excel for the Mac from the Get Data command on the Data tab. Once enabled, Power Query functionality is available from the Power Query tab on the Excel ribbon.

Do I have to download Power Query?

Power Query is a free add-in for Excel 2016 and beyond, as well as a built-in Excel feature for Microsoft 365. If your organization uses those versions of Excel, you do not need to  download Power Query . You just need to click the button named  Get Data  in the  Get & Transform  section in the  Data  tab. 

What to consider when using Power Query

• As you enter the Power Query interface, you will no longer be able to interact with Excel. If you need to return to Excel, you will have to close the Power Query editor.
• The Microsoft Power Query add-in for Excel 2010/2013 is no longer being updated by Microsoft.  If you are using Excel 2010/2013, you should upgrade to a newer version of Excel to use Power Query.

If you are regularly cleaning and transforming data in Excel, we recommend pinning the Power Query Editor to your quick access toolbar in Excel.

How does Power Query work?

You can open the Power Query editor by clicking on transform data. It will open in a separate window. Here’s how to navigate Power Query and what you’ll find in each tab:

Data ribbon: search for data sources.

Queries and Connections pane: have a quick look at all of the connections (queries) you made.

Once you launch Power Query, you land on the Power Query Editor which is made up of four distinct elements :

Main ribbon: create queries from scratch thanks to its buttons (if you are familiar with the M language, you can also access the Advanced Editor to tweak them manually).

List of queries: keep track of all connections to data sources and streams.

Data Table preview: to visualize your data result after a specific step.

Query Settings (Applied Steps): navigate through all the steps you made. Power Query automatically records all transformations made.

That’s all for this introduction to Power Query. To discover more about how Power Query can improve data transformation and analytics within your organization, see the resources below.

For more tips on how to boost your efficiency within the Microsoft 365 suite, bookmark our Knowledge Hub or follow us on LinkedIn.

Top resources to learn more about Power Query:

• How to automate data tasks in Excel using Power Query – Excel Campus
• How to use Microsoft Power Query – Kevin Stratvert
• How Power Query will change the way you use Excel – Leila Gharani
• Power Query challenges  – Ken Puls
• Combine Excel Files with Power Query – Miguel Escobar

Efficiency Tips How to guides Power BI

• Efficiency Tips|How to guides|Technology and AI

The 2024 guide to PowerPoint add ins

PowerPoint is the cornerstone of business communication strategy and collaboration with most of us spending...

• Efficiency Tips|Excel|Finance insights

5 ways to make beautiful financial charts and graphs in Excel

Financial charts and graphs are the foundation of every pitchbook and report in the industry...

• Efficiency Tips|How to guides

How to reduce your Excel file size in 8 easy ways

Did you know a staggering nbsp 30 of finance professionals spend 4 hours in Excel...

Find out how UpSlide could help you and your teams

• Document automation
• Brand compliance
• Reporting automation
• Content enablement
• Enterprise solution
• PowerPoint automation tool
• Implementation
• Design studio
• Investment banking
• Asset management
• Private equity
• UpSlide vs Templafy
• UpSlide vs Macabacus

Top features

• Excel to PowerPoint Link
• Content Library
• Slide Check
• PowerPoint Formatting Features
• Power BI to PowerPoint Link
• Template Management
• Excel Charts
• Outlook Signature Manager
• Knowledge hub
• Help center
• UpSlide Portal
• ROI calculator
• Sustainability
• Subscribe to our newsletter
• Legal terms
• User information
• © 2022-2024 UpSlide. All rights reserved.
• User informations

• Courses Business Charts in Excel Master Excel Power Query – Beginner to Pro Fast Track to Power BI View all Courses
• For Business

• Power Excel
• Dashboards, Charts & Features
• VBA & Scripts

Business Charts in Excel course is NOW AVAILABLE! Discover more

Create Business charts that grab attention AND auto-update. Wow your coworkers and managers with smart time-saving techniques.

Power Query is essential for Excel users who work with lots of data. This course teaches you how to use Excel in Power Mode and create meaningful reports with far less effort.

Stay ahead of the game in 2024. Get access to our best-selling Power BI course now and become a highly sought-after Power BI professional. This course gets you started in Power BI – Fast!

How to Strike Through Text in Excel

Using strikethrough text in Excel is a handy way to mark completed tasks.

It can also show changes or errors without deleting the original text. We even have automatic solutions to make your workflow smoother.

Download our free workbook to immediately apply these techniques to your data.

In this tutorial:

• Typical Uses for Strikethrough Text
• Method 1: Font Settings
• Method 2: Keyboard Shortcut
• Method 3: Quick Access Toolbar (QAT)
• Method 4: Conditional Formatting
• Method 5: Checkbox in Excel
• Download the Workbook

Grab our practice workbook 👉 HERE and follow along:

A strikethrough is a line through the middle of text. It shows that the text is no longer valid or has been completed. This method keeps the text visible but marked as finished or changed.

• Task Lists : Mark tasks as completed without removing them.
• Editing : Show revisions while keeping the original text visible.
• Errors : Indicate mistakes that have been noted but not removed.
• Ideas : Cross out ideas that have been considered but not selected.

Let’s look at different methods of how to strikethrough in Excel.

Imagine you have a learning list, and you want to mark each completed item with strikethrough text.

• Select the cell or multiple cells you want to strikethrough.
• Right-click and choose “ Format Cells …”

• Go to the “ Font ” tab.
• Check the “ Strikethrough ” box.

• Click “OK” and Excel will put a line through the text.

The quickest solution to cross out text is to use the Excel strikethrough shortcut.

• Select the cell or text.
• Press Ctrl + 5 (Command + Shift + X on Mac).

The strikethrough format will be applied to the selected cell(s) instantly.

Another quick solution is to add a Strikethrough button to the Quick Access Toolbar (QAT) on the Excel ribbon. This makes strikethrough formatting just a click away.

• Click the dropdown arrow at the right end of the Quick Access Toolbar.

• Select “More Commands…” from the dropdown menu.

• In the Excel Options menu, set the “Choose commands from” dropdown to “ All Commands .”

• Scroll down the list, select “Strikethrough”. Then click the “ Add ” button to move it to the QAT.
• Click “OK” to apply the changes.

The Strikethrough icon will now appear in your Quick Access Toolbar. Highlight the cells you want to format and click this icon to apply the strikethrough.

You can use conditional formatting to add strikethrough to cells automatically. For example, mark a task as “done” in a nearby cell.

The task text will then show a strikethrough. Here’s how:

• Select the cells with your tasks where you want to apply conditional strikethrough.
• Go to the “Home” tab on the Ribbon.
• Click on “ Conditional Formatting ” in the “Styles” group.
• Choose “ New Rule ” from the dropdown menu.

• In the “New Formatting Rule” dialog box, select “ Use a formula to determine which cells to format .”
• In the “Format values where this formula is true” box, enter this formula (adjust the cell reference for your data):

💡 The dollar sign before the column letter ensures that the reference to column C remains fixed, while the row number can change based on the selected cells.

• Then click the “ Format ” button to open the “Format Cells” dialog box.

• Go to the “Font” tab in the “Format Cells” dialog box.
• Check the “ Strikethrough ” box. Optionally, you can make other formatting changes, such as setting a light grey font color for crossed-out entries.
• Click “OK” to close the “Format Cells” dialog box.

• Click “OK” again to close the “New Formatting Rule” dialog box.

Now, when you type “done” in the cell in column C for a task, Excel will apply the strikethrough format to the task text in the cell.

You can use checkboxes to mark tasks as done. Insert checkboxes in Excel and link them to cells (which you can hide later). Base your conditional formatting rule on the linked cells (TRUE if checked, FALSE if not).

Excel will automatically strike through tasks when the checkbox is selected.

In addition to this, we have added a resulting list “Left to Learn” next to it. This list shows the remaining tasks using the FILTER function in Excel.

For detailed steps to create this, check out the detailed article here . This method is also in the workbook you can download below. 👇

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master how to strike through text in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.

Featured Course

Excel Essentials for the Real World

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.

Need help deciding?

Find your ideal course with this quick quiz. Takes one minute.

Business Charts in Excel

Master Excel Power Query – Beginner to Pro

Featured tutorials.

Black Belt Excel Package

You might also like...

How to Remove Blank Rows in Excel

How to Use SUMIF Function in Excel

How to Insert a Check Mark in Excel

EXCLUSIVE FREE NEWSLETTER

Join between the sheets.

Kickstart your week with our free newsletter covering Excel hacks, Power BI tips, and the latest in AI. You get to stay updated and get all the insights you need, delivered straight to your inbox.

You can unsubscribe anytime of course.

Stay Ahead with Weekly Insights!

Dive into Excel, AI and other essential tech news:

carefully crafted for the modern professional.

Success! Now check your email to confirm your subscription.

There was an error submitting your subscription. Please try again.

• Skip to main content
• Skip to header right navigation
• Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Automatically update data in another workbook|Power Query|Excel Forum|My Online Training Hub

Please for your help!

I have two worksheets, one is the source and the second one is the destination. In the source workbook I have a list of project names. The project names are listed in column A . Adjacent to column A, are in columns the months, where the employees will manually add the percentage of hours they worked for each project for that month.

I managed with power query to link the data in the destination file so when I add a new project in the source workbook automatically is added in the correct row of the column A in the destination workbook. However, when a new project is added in the destination workbook, creates only a cell and not the whole row. I do not want to link the whole table (this would have added a new row), I want the new row to be empty and ready to be filled by a user.

How can I automatically create a new row with filled the column A and empty the rest columns of the row when a new project is added in the source workbook?

Many thanks in advance!

Not sure I understand what you mean by:

"I managed with power query to link the data in the destination file so when I add a new project in the source workbook automatically is added in the correct row of the column A in the destination workbook."

What exactly did you do in PQ? And, could you perhaps upload a file with som example data.

• Draft and add content
• Rewrite text
• Chat with Copilot
• Create a summary
• Copilot in Word on mobile devices
• Frequently asked questions
• Create a new presentation
• Add a slide or image
• Summarize your presentation
• Organize your presentation
• Use your organization's branding
• Copilot in PowerPoint for mobile devices
• Draft an Outlook email message
• Summarize an email thread
• Suggested drafts in Outlook
• Email coaching
• Get started with Copilot in Excel
• Identify insights
• Highlight, sort, and filter your data
• Generate formula columns
• Summarize your OneNote notes
• Create a to-do list and tasks
• Create project plans in OneNote

Create a new presentation with Copilot in PowerPoint

Note:  This feature is available to customers with a Copilot for Microsoft 365 license or Copilot Pro license.

Create a new presentation in PowerPoint.

Select Send . Copilot will draft a presentation for you!

Edit the presentation to suit your needs, ask Copilot to add a slide , or start over with a new presentation and refine your prompt to include more specifics. For example, "Create a presentation about hybrid meeting best practices that includes examples for team building.”

Create a presentation with a template

Note:  This feature is only available to customers with a Copilot for Microsoft 365 (work) license. It is not currently available to customers with a Copilot Pro (home) license.

Copilot can use your existing themes and templates to create a presentation. Learn more about making your presentations look great with Copilot in PowerPoint .

Enter your prompt or select Create presentation from file to create a first draft of your presentation using your theme or template.

Edit the presentation to suit your needs, ask Copilot to add a slide , organize your presentation, or add images.

Create a presentation from a file with Copilot

Note:  This feature is only available to customers with a Copilot for Microsoft 365 (work) license. It is not currently available to customers with a Copilot Pro (home) license.

With Copilot in PowerPoint, you can create a presentation from an existing Word document. Point Copilot in PowerPoint to your Word document, and it will generate slides, apply layouts, create speaker notes, and choose a theme for you.

Select the Word document you want from the picker that appears. If you don't see the document you want, start typing any part of the filename to search for it.

Note:  If the file picker doesn't appear type a front slash (/) to cause it to pop up.

Best practices when creating a presentation from a Word document

Leverage word styles to help copilot understand the structure of your document.

By using Styles in Word to organize your document, Copilot will better understand your document structure and how to break it up into slides of a presentation. Structure your content under Titles and Headers when appropriate and Copilot will do its best to generate a presentation for you.

Include images that are relevant to your presentation

When creating a presentation, Copilot will try to incorporate the images in your Word document. If you have images that you would like to be brought over to your presentation, be sure to include them in your Word document.

Start with your organization’s template

If your organization uses a standard template, start with this file before creating a presentation with Copilot. Starting with a template will let Copilot know that you would like to retain the presentation’s theme and design. Copilot will use existing layouts to build a presentation for you. Learn more about Making your presentations look great with Copilot in PowerPoint .

Tip:  Copilot works best with Word documents that are less than 24 MB.

Welcome to Copilot in PowerPoint

Frequently Asked Questions about Copilot in PowerPoint

Where can I get Microsoft Copilot?

Copilot Lab - Start your Copilot journey

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Find solutions to common problems or get help from a support agent.

Online support

Was this information helpful?

Thank you for your feedback.

• デジタルトランスフォーメーション

Ｓｋｙの関連記事はこちらから - PR -

• Power Query(パワークエリ)の概要と具体的な活用例

掲載日  2024/05/13 11:00

ノーコードで自動化を実現するパワークエリ

簡単だけど少し面倒なexcel作業の例, パワークエリを使用した場合, クエリの更新によるデータ処理の自動化.

近年、手間のかかる作業をPCに処理させる「自動化」が注目を集めている。Excelを自動化するために、VBAに挑戦してみた方もいるだろう。しかし、VBAはプログラミング言語の一種になるため、得手／不得手があるのも事実。そこで、この連載では「ノーコード」で自動化を実現できるPower Query(パワークエリ)の使い方を詳しく紹介していこう。

Power Query(パワークエリ)の概要

Excelはビジネスに必須のアプリであり、詳しくなればなるほど便利で快適なツールとして活用できるようになる。しかし、どんなにExcelスキルを磨いても、状況によっては「面倒で単調な作業」を強いられてしまうケースがある。

このような場合にぜひ覚えておきたいのが「パワークエリ」と呼ばれるツールだ。パワークエリはExcelに標準装備されているツールで、サブスク版のMicrosoft 365はもちろん、Excel 2021/2019/2016で使用することが可能となっている。

このツールを上手に使うと、日々のExcel作業を自動化できるようになる。パワークエリは「ノーコード」でも使えるように設計されているため、プログラミングが苦手でVBAに挫折した経験がある方でも十分に対応できるツールとなる。

ということで、「パワークエリ」の詳しい使い方を本連載で紹介していこう。この出会いをきっかけに「Excelの新しい活用方法」を発見して頂ければ幸いだ。

なお、本連載では、Excel 2021をベースにパワークエリの使い方を解説していく。他のバージョンのExcelでは画面表示などが若干異なる部分もあるが、基本的な操作手順は同じと考えてよい。よって、Excel 2021以外を使用している場合でも十分に参考になるはずだ。

まずは、パワークエリの概要を知るために、具体的な例を紹介していこう。以下の図は、あるハンバーガー店が日々の売上データをExcelで記録したものだ。売上データは1日ずつ別のExcelファイルに記録されており、現時点では3日分のデータ(4月1日～4月3日)が保存されている。

日々の売上データを記録したExcelファイル

各ファイルの内容を紹介していこう。各日の売上データは、提供方法(店内飲食／テイクアウト)や分類(バーガー類／サイドメニュー／ソフトドリンク／アルコール)に分けて記録されている。たとえば、「4月1日の売上」のExcelファイルには、以下の図のような形式で売上データが記録されている。

他の日付もデータ表の形式は同じで、それぞれの項目について「数量」と「売上」を記録していく仕組みになっている。念のため、「4月2日の売上」と「4月3日の売上」についてもファイルの中身を紹介しておこう。

さて、ここからが本題だ。これらのデータをもとに、3日分の「売上の合計」や「提供方法別の合計」など、さまざまな分析を行いたい場合はどうすればよいだろうか？　通常のExcel操作で作業を進めていくのであれば、たいていの場合、以下のような作業手順になると思われる。

白紙のExcel(ワークシート)を作成し、そこに「4月1日の売上」のデータをコピー&ペーストする。

「4月1日の売上データ」をコピー&ペースト

このままでは「日付」の情報が抜け落ちてしまうので、この表に「4月1日」のデータを手入力で追加する。

続いて、「4月2日の売上」のExcelファイルを開き、データの部分だけをコピー&ペーストする。さらに「4月2日」の日付を手入力で追加する。

「4月2日の売上データ」を追加

以降も同様に、「4月3日の売上」のデータをコピー&ペーストし、日付を手入力してあげる。すると、以下の図に示したようなデータ表を作成できる。

「4月3日の売上データ」を追加

これで準備は完了。あとは、各自の好きなようにデータを分析していけばよい。3日間の「売上の合計」を求めたい場合は、関数SUMを使って「F3:F23」のセル範囲を合計する。

関数SUMで「売上」の合計を算出

「提供方法別の合計」は関数SUMIFで求められる。たとえば、「店内飲食」についてのみ合計を求めたい場合は、以下の図のように関数SUMIFを記述すればよい。

関数SUMIFで「店内飲食」の合計を算出

同様の手順で「テイクアウト」についても「売上」の合計を求めると、以下の図のような結果を得ることができる。

「提供方法」で分類した合計

このように、複数のファイルに分割して記録されているデータを分析するときは、コピー&ペーストなどによりデータ表を結合する作業が必要となる。今回の例のように、Excelファイルの数が3つ程度であれば、上記に示した方法で作業を進めても特に問題は生じないだろう。

ただし、「3月の売上データも同じ形式で記録されていた……」となると、少し話が変わってくる。3月は全部で31日あるので、

(1)各日付のExcelファイルを開いてデータをコピーする (2)集計用のExcelファイルに、先ほどコピーしたデータを貼り付ける (3)コピー元のExcelファイルを閉じる (4)日付データを手作業で追加する

といった作業を31回も繰り返さなければならない。無理な話ではないが、少し面倒な作業になると予想される。

さらに、「実は昨年1年分のデータも同じ形式で……」となると、同様の作業を365回も繰り返す羽目になってしまう。これは相当に面倒な作業になるはずだ。また、ミスなく365回もコピペを繰り返す、というのも不安要素になる。もしかしたら途中でコピーミスを犯してしまい、それに気づかずに作業を進めてしまう可能性も十分に考えられる。

このような場合にぜひ活用したいのが、本連載で紹介するパワークエリだ。パワークエリを使えば、クリックひとつで複数のExcelファイルを「1つのデータ表」に結合することが可能となる。具体的には、「データ」タブにある「すべて更新」をクリックするだけでデータ分析の前準備を完了できることになる。

パワークエリで結合したデータ表

あとは、目的に応じでデータを分析していくだけ。3日分の「売上の合計」は関数SUMで算出できる。

同様に関数SUMIFで「提供方法別の合計」を求めてもよい。以降の分析作業を「どのように進めていくか？」は各自の自由だ。

このように、面倒な繰り返し処理を「自動化」できるツールがパワークエリとなる。ただし、パワークエリが勝手に状況を判断して、自動的に処理を行ってくれる訳ではない。自動化を実現するには、「どのような手順で処理を進めていくか？」を事前にクエリとして指示しておく必要がある。これを指示するための操作画面が「Power Query エディター」となる。

Power Query エディター

(1)「4月の売上」フォルダー内にあるExcelファイルを結合する (2)結合したデータ表から「合計」の行を削除する (3)「日付」のデータを追加する

といった処理をPower Query エディターで指示している。つまり、パワークエリを活用するにはPower Query エディターの使い方を習得しておく必要がある訳だ。この操作方法について詳しく紹介していくのが本連載の主旨となる。

先ほど示したPower Query エディターの処理手順をよく見ると、「3つのExcelファイルを結合する」ではなく、「フォルダー内にあるExcelファイルを結合する」という指示になっていることに気付くと思う。

このため、Excelファイルの数が増えたときも、そのままパワークエリで処理することが可能である。続いては、「4月4日」と「4月5日」の売上データをフォルダーに追加した例を見ていこう。

フォルダーに「4月4日」と「4月5日」の売上データを追加

パワークエリで結合したデータ表を開き、「データ」タブにある「すべて更新」をクリックすると……、

「4月4日」と「4月5日」の売上データが自動的に追加されるのを確認できる。それにあわせて「合計」や「店内飲食」、「テイクアウト」の数値も再計算される。

自動集計された売上データ

もちろん、4月6日以降についても同様だ。各日の売上データを記録したExcelファイルをフォルダーに追加するだけで、最新の分析結果を得ることが可能となる。

このように、データの追加・修正に柔軟に対応できることもパワークエリの大きな魅力となる。そのつど新しいデータをコピー&ペーストしてあげる必要はない。クリックひとつで“最新の状況”に即した結果を得られるようになる。

そのほか、毎日のように繰り返す作業ではないが、「通常のExcelでは処理するのが難しい……」といった作業にパワークエリを活用できるケースもある。

Excelで複雑な処理を行うには“関数”の利用が必須になる。状況によっては「関数をいくつも組み合わせて処理しなければならない」というケースもあるだろう。関数に不慣れな方にとって、これは大きな障壁となるかもしれない。

このような場合にパワークエリを活用すると、もっと理解しやすい形で処理を進めていくことが可能となる。関数の知識がなくても大丈夫。パワークエリは、それぞれの処理をステップ形式で指示していく仕組みになっているため、各ステップの結果を見ながら処理を進めていくことが可能となる。

そういった使い方も含めて、次回の連載から「Power Query エディター」の操作手順について詳しく解説していこう。パワークエリを使いこなすには、多少の“知識”と“慣れ”が求められる。本連載を読み進めることで、少しでも多くのスキルを習得して頂ければ幸いだ。

• 第1回 Power Query(パワークエリ)の概要と具体的な活用例

Members+ 会員限定記事

PythonでExcel作業の効率化を図ろう 第16回 PythonからExcelｌの罫線を引く(細、太、二重、点線、破線、破点線)

軍事とIT 第558回 システムの統合化(5)イージス・システムの場合

“Why”を追求せよ! ミツカン COOが考える新規事業開発の在り方

LINEヤフー若手エンジニアに聞いた、新卒スタートダッシュのヒントとは？

DDoS攻撃を受けた業界第1位はIT＆情報通信、押さえておくべき2つのトレンド

パナソニックHD、EV電池へ投資加速 楠見社長「2桁ROICを維持できる体質へ」

本当にあったエンジニアの転職体験談

職場で「好かれる」「嫌われる」行動パターン5つ！　共通点は○○だった

AI時代に「必要とされる人材」とは？?」4月1日より…

71％の人が仕事を辞めたいと思った経験あり。その理由と対処法は？

あなたが「本領発揮できる職種」をサクっと診断！

エンジニアあるある!?　システム開発現場・実録IT用語辞典

Excelをノーコードで自動化しよう! パワークエリの教科書 第1回 Power Query(パワークエリ)の概要と具体的な活用例

定時で上がろう! Excel関数の底力 第60回 XLOOKUPやVLOOKUPで「複数の検索値」を指定する方法

定時で上がろう! Excel関数の底力 第71回 “動的なグラフ”の作り方、関数SERIESを上手に活用

弥生の給与計算ソフトが「定額減税」に対応する機能を提供開始

定時で上がろう! Excel関数の底力 第64回 変数を使って数式などを処理する、関数LETの使い方

脱「パワポっぽい」 Canvaでオシャレなプレゼンを 第1回 Canvaとは? 何ができるWebサービスなのか

作り方で変わる！Excelグラフ実践テク 第34回 意外と難しい「見やすい散布図」の作り方

脱初心者！すぐに使えるPowerPoint活用術 第1回 PowerPointの使い道は意外と多い！？さまざまなビジネスシーンでの活用術

※本記事は掲載時点の情報であり、最新のものとは異なる場合があります。予めご了承ください。

プロジェクト型業務の収支管理課題を解決する統合型基幹業務システム──クラウドで安価かつ手軽に利用でき、独自のこだわり機能をカスタマイズで追加可能

日本におけるイノベーションの浸透とそれを阻むものとは？アサナジャパンがレポート, ソニーの「マネージドクラウド with aws」にamazon bedrock用いたfaq機能を追加, oracle fusion data intelligenceに新たなai機能、erpやscmの分析強化, legalon cloud、法務相談の法令とガイドライン検索の円滑化を支援する新機能, このカテゴリーについて.

統合業務やマネジメントなど、企業の経営資源の有効活用に向けたサービスやソリューションを紹介していきます。