Link access query to excel 2022

Are you dealing with data from a bunch of different places and combine them on a regular basis to do analysis or reporting? Excel Power Query may be the solution you’re looking for! The best thing about this tool is that you can fully automate your data loading and cleaning procedures with a click of a button. 

In this tutorial, you’ll learn what Power Query can do and how powerful its features really are. We also provide some practical examples that you can follow to understand basic data transformations using this tool. Let’s get started!

What is Power Query in Excel?

Power Query is a business intelligence tool in Excel used to carry out the ETL (Extract, Transform, and Load) process. This process involves getting data from a source, transforming it, then placing it to a destination for analysis. ETL is known as a crucial step in building a data warehouse, but actually, you’re doing ETL-like processes even if you’re just doing a weekly or monthly report.

What can Power Query do?

With Power Query, everyone can deliver meaningful insight quickly using Excel. There was a time when BI processes required dedicated teams of IT specialists, but not anymore. You can use Power Query as part of your self-service ETL solution to do the following tasks:

#1. Extract (connect and get) data from a source

Power Query allows you to connect instantly with a wide range of data in different formats and locations. Whether your data is in CSV, XML, JSON, or PDF formats, that’s not a problem. Your organization stores data in Azure SQL Database, IBM DB2, Oracle, or PostgreSQL? You can easily access them. Even if you use platforms such as Salesforce and MS Dynamics 365, just connect straight away without hassle!

#2. Transform your data to make it ready for analysis

After connecting to a data source, you may need to modify the data in several ways. Data transformation is the area where Power Query shines. This tool allows for a range of operations, from simple data transformation tasks to the most complex data restructuring challenges, in just a few clicks.

Examples of data transformation tasks:

• Data cleaning: Remove duplicates, change data types and formatting, filter rows, split columns, and pivot/unpivot columns.
• Data integration: Join or split source tables, add lookup keys, and aggregate data.
• Data enrichment: Extend the source data by creating calculated columns.

The ones mentioned above merely scratches the surface of all that Power Query can do to transform your data. The best thing about this tool is that you can automate those data transformation tasks using a code-free interface — without macro or VBA codes.  

#3. Load transformed data into a worksheet or the Data Model

After your data is clean and ready for analysis, Power Query Excel gives you options to load your data into one or both of these destinations:

• A worksheet. By default, Power Query lands the output data directly in a new worksheet inside your Excel file. If you want, you can place data from each source into a separate worksheet and then do whatever you want with it, just as if it were “normal” Excel data.
• The Data Model. Your data is compressed and stored in memory. With this option, you can work with millions, tens of millions, even hundreds of millions of rows of data, exceeding the 1,048,576 row limit of an Excel worksheet!

The Data Model is normally used as the basis for pivot table output in Excel. Thus, it’s also referred to as the Power Pivot Data Model. This article won’t be covering the Data Model and Power Pivot in more detail, as those are broad subjects.

Why use Excel Power Query?

Not only does Power Query allow you to get and transform your data, but this tool also records all the steps applied.

You can refresh all the processes such as re-import the source data, reapply all the data filtering, sorting, and other transformations that you defined — in a single click. So, once all of that’s set up, you don’t need to create it again. Of course, you can also go back and edit each step, and even add steps in between. 

This is all done within a tool you’re already familiar with: Excel. 

Power Query in different versions of Excel

Power Query for Excel was initially released as an add-in to download and install for Excel 2010 and 2013. After you add Power Query to Excel, a new tab named Power Query will appear in the Excel ribbon.

This tool was fully integrated into Excel by the 2016 version and accessed under the Get & Transform section in the Data tab. So if you use the latest versions of Excel, you already have Power Query integrated within Excel. 

The following image summarizes where you can find Power Query in different versions of Excel. Please note that each build of Excel may be slightly different — so you might see slightly different icons.

We use Office 365 in this tutorial, however, you can follow the steps described in this article with earlier versions of the product. The entry point into Power Query may be different, but this should not cause any significant difficulties.

Excel Power Query: Download sample files

We provide a small set of sample data used in the examples throughout this article. Just download files from this link to make it easier for you to follow along:

Download sample files

After that, put the CSVs in a folder, for example in “D:/Power Query/Sample files”.

How to use Power Query to GET and LOAD data into Excel

Let’s begin with a quick overview of Power Query’s list of data sources. After that, we’ll get some data into Excel and look into more detail about the Power Query interface.

Power Query list of data sources

Go to the Data tab and locate Power Query in the Get & Transform Data section. Click on the Get Data button — you will see a dropdown menu to select your data source:

Please note that the range of available Power Query data sources will depend on the version of Excel that you are using. 

Data source options

• From File: Excel, TXT/CSV, XML, JSON, and PDF.
• From Database: SQL Server, Access, Oracle, DB2, MySQL, PostgreSQL, Sybase, Teradata, and SAP Hana.
• From Azure: Azure SQL Database, Azure Synapse Analytics, Azure HDInsight (HDFS), Azure Blob, Azure Table, and Azure Data Lake Storage.
• From Online Services: Sharepoint Online, Exchange Online, Dynamics 365, Salesforce Objects, and Salesforce Reports.
• From Other Sources: Excel Table/Range, Web, OData Feed, ODBC, OLEDB, Active Directory, etc.

If you take a closer look at the Get Data options, you will find that there are currently around 40 data sources for which Power Query connectors are available. However, even this number is small compared to the number of potential data sources out there.

What can you do if your data source is not among those currently available?

One solution is by using a generic data connector such as OData Feed, OLE DB, and ODBC. Another solution is to use an integration tool to help you seamlessly connect and get data from external apps into Excel. An example of this is by using Coupler.io, which is a solution to import data from various apps such as Airtable, Shopify, Jira, QuickBooks, Pipedrive, Hubspot, and more! 

Check out the complete list of Coupler.io’s Excel integrations.

A simple example: Get data from CSV files into Excel using Power Query

In the following example, we will import data from two downloaded CSV files one by one and load them into new worksheets. 

First, we’ll show you how to get and load Sales.csv directly into a new worksheet. After that, we’ll show you how to import Products.csv and open it in the Power Query Editor before loading it. Here are the steps:

1. Open a new blank Excel workbook.
2. Click the Data tab, then click the Get Data button in the Get & Transform Data section.
3. In the dropdown, select From File > From TXT/CVS.

1. Browse the folder where you downloaded the sample files. Then, select Sales.csv and click Import.

1. In the Preview window, click Load to load the data into a new worksheet. 

You will see a new worksheet inside the current workbook, as shown in the below screenshot. Your external data is now an Excel table. On the right pane, notice that there is a query to your data source listed there.

1. Import Products.csv by repeating Steps 1-4 above, but this time, don’t forget to select Products.csv instead of Sales.csv.
2. In the Preview window, click Transform Data. 

This will open the Power Query Editor as shown in the following screenshot:

As you can see, clicking Transform Data will bring you to a different, separate interface called Power Query Editor. This editor allows you to transform your data before loading it into a new worksheet. 

We’ll cover more detail about the Power Query Editor in the next section. For now, let’s not do any data transformations here. We’ll continue to load the products data into a new worksheet from this editor.

1. Click the small triangle icon in the Close & Load button in the Home tab. Select the first option in the dropdown. 

Note: If you choose the second option, you’ll get more options to load your data — more about this in the Load To… options section.

As the final result, you will see a new worksheet created containing the Products table. If you notice, there are two queries listed on the right pane: Sales and Products.

You’ve learned how to get and load two CSV files directly to Excel using Power Query. By the way, you can do something similar using Coupler.io as it includes a CSV to Excel integration as well. You can even set up automatic data refresh on schedule, such as hourly, weekly, and monthly.

Try Coupler.io for free with the seamless Excel integration

Load To… options in Excel Power Query

As explained previously, Power Query provides you with two options to load data: to a worksheet and/or data model. If you want to load data into a worksheet, there are several variations if you choose the Load To… option:

As shown in the above dialog, you can:

• Load into an Excel named table (the default)
• Load into a pivot table based on the source data
• Load into a pivot chart based on the source data
• Only create a connection to the data, but do not load it yet

Notice that on top of this, you have the choice of whether you want to create the table of data, pivot table, or pivot chart in an existing or new worksheet. 

If you also want to add the data to the Data Model, tick the Add this data to the Data Model checkbox.

Excel Power Query Editor

The Power Query Editor is a separate interface from Excel. All of your data transformations will happen in this editor, which can be launched in one of these two ways:

• Click the Get Data button then select Launch Power Query Editor…
• Double-click a query listed in the Queries & Connections pane.

Here are the six main elements of Power Query Editor:

1. The Ribbon. It has 5 main tabs: File, Home, Transform, Add Column, and View.
2. Query List. This pane contains all the queries that have been added to the current workbook. You can navigate to any query from this area to begin editing it. 
3. Data Preview. This area is where you can see a sample of the data for a selected query.
4. Formula Bar. This area shows the M code of the current transformation step. Power Query records each of your transformation steps into the M code that you can see in this formula bar. Most of the time, you don’t need to use the M language directly at all. 
5. Properties. This is where you can see and edit the properties of your query. For example, you can rename your query, add a description to it, and enable fast data loading.
6. Applied Steps. This area contains a list of steps used to transform data.

How to use Power Query to TRANSFORM data in Excel

The range of transformations that Power Query offers are wide and varied. It can be initially daunting if you’re unfamiliar with the feature set available in this tool, but don’t worry! We’ve selected some simple, practical examples for you:

Excel Power Query: Remove duplicates

An external source of data might not be as flawless as you expect. The presence of duplicates is one of the most annoying characteristics of poor quality data.

If you look closely at the Products table, you’ll notice two products with ProductNumber DS803.

To remove the above duplicates, follow the steps below:

1. Launch the Power Query Editor and make sure to select the Products query.

1. In the Home tab, click Remove Rows > Remove Duplicates.

1. Notice that one of the rows with ProductNumber DS803 is now removed and a step added in the APPLIED STEPS pane:

1. Click the Close & Load button. This will refresh the Products table in your worksheet.

Excel Power Query: Create parameters for folder paths

In the Power Query Editor, let’s open the Products query and click on the first row “Source” in the APPLIED STEPS. You will see a hard-coded file path like shown below:

If you check on the Sales query, you’ll notice that it also uses a fixed value for the file path, i.e., D:\Power Query\Sample files\Sales.csv

Changing the folder path to use a parameter can be a time-saver in the future. In case you need to move your files to another folder later, you’ll only need to change the parameter value once. 

Let’s do the following steps to replace the hard-coded folder path with a parameter:

1. In the Home tab, click Manage Parameters > New Parameter.

1. Create a new parameter using the following details, then click OK.
   1. Name: FolderPath
   2. Required: ✔
   3. Type: Text
   4. Suggested Values: Any value
   5. Current Value: D:\Power Query\Sample files\ 

1. Select the Products query, then click “Source” in the APPLIED STEPS. 
2. In the formula bar, replace the folder path to use the FolderPath parameter:

FolderPath & "Products.csv"

1. Now, select the Sales query, then click “Source” in the APPLIED STEPS.
2. In the formula bar, replace the folder path to use the FolderPath parameter:

FolderPath & "Sales.csv"

Now, you’ve changed the file path of both queries to use the FolderPath parameter. Please be aware that the code in the formula bar is case-sensitive. Also, notice that you don’t need to enclose parameters with double quotes.

Excel Power Query: Adding a conditional column with IF statement

Suppose you want to create a new column, i.e., Category in the Products query, that tells you which category each product belongs to. The first 2 digits of the product number identify the product category based on the following rules:

• Product number begins with “DS” → Daisy
• Product number begins with “OC” → Orchid
• Product number begins with “RS” → Rose
• Product number begins with “SF” → Sunflower

Here’s how you can add the Category column:

1. Open the Power Query Editor and select the Products query. 
2. Click the Add Column tab, then click Conditional Column.

1. In the “Add Conditional Column” dialog that appears, enter the following details, then click OK when done.

1. Click File > Close & Load. 
2. Notice that your worksheet containing the Products table will have the new Category column, as shown below:

Excel Power Query: Drill-down to create parameters from cell

Suppose you want to be able to filter products by category from a cell as shown in the below image:

To pass the value from cell B3 to the query and use it to filter the products, follow the steps below:

1. Create a new worksheet, e.g. Sheet4, then add the following details:
   1. Cell B2: A text “Category”.
   2. Cell B3: A dropdown containing a list of product categories: Daisy, Orchid, Rose, and Sunflower. You can create the dropdown using Data > Data Validation with details as follows:

1. Click Data > From Table/Range.

1. In the “Create Table” dialog, enter the following details, then click OK.
   1. Table range: =$B$2:$B$3
   2. My table has headers: ✔

1. In the Power Query Editor that opens, rename the new query to CategoryFromCell.

1. Right-click on the data and select Drill Down.

1. Click File > Close & Load To… 
2. In the “Import Data” dialog, select Only Create Connection.

1. Reopen the Power Query Editor.
2. Right-click on the Products query and select Duplicate. Rename the new query as ProductsByCategory.

1. Add a filter by category by selecting the small triangle icon in the Category column, then choose Text Filters > Equals.

1.  In the Filter Rows dialog, type “Daisy”, then click OK.

1. In the Formula Bar, change the text “Daisy” to use the CategoryFromCell parameter.

1. Click File > Close & Load To… Then, in the “Import Data” dialog, select to load to the existing worksheet Sheet4!$B$5 and click OK when done.

Your final worksheet will look like this below. Test by changing the dropdown value to “Rose”, then click the Refresh All button. 

Excel Power Query: Merge tables

Merging queries allows you to join tables based on a key column. This is like using VLOOKUP Excel. For example, here we’re going to merge the Sales and Products queries into one. We will retrieve columns from the Products table (the lookup table) and pull them into the Sales table. 

Here are the steps: 

1. Launch the Power Query Editor and select the Sales query.
2. Click Merge Queries in the Home tab, then select Merge Queries as New. 

Note: As you can see, you have two options for merging. You can either overwrite the current Sales query with additional columns or merge two queries as a new one to keep your current Sales table unchanged.

1. In the “Merge” dialog box, select Products as the second table. Then, select the ProductNumber column for both tables and click OK.

1. If you want, rename the new query as ProductSales_Merge by changing the query name in the Properties pane.
2. Expand the Products table and select ProductName, Price, and Category columns to include in the query.

1. If you want, reorder the columns by moving them to the position you want, the same way you move columns in Excel.

Here’s an example after we moved the ProductNumber, Category, and Price columns before the Quantity and Discount columns.

1. Click File > Close & Load to load the query into a new worksheet.

Excel Power Query: Using formulas

Suppose that in the ProductSales_Merge query, we want to add a new column OrderTotal which is calculated from other columns using this formula:

OrderTotal = Price * Quantity - Discount

To do that, follow the steps below:

1. Launch the Power Query Editor and select the ProductSales_Merge query.
2. In the Add Column tab, click Custom Column.

1. In the “Custom Column” dialog that appears, enter the following details then click OK.
   1. New column name: OrderTotal
   2. Custom column formula: = [Price] * [Quantity] - [Discount]

1. Notice that a new column OrderTotal was added:

1. Click File > Close & Load.

Excel Power Query: Using functions

In this last example, we’ll show you how to use functions in Power Query. We’ll add a new column Quarter that represents the number of the quarter from the order dates.

1. Launch the Power Query Editor and select the ProductSales_Merge query.
2. In the Add Column tab, click Custom Column.
3. In the “Custom Column” dialog that appears, enter the following details, then click OK.
   1. New column name: Quarter
   2. Custom column formula:
      = "Q"&Number.ToText(Date.QuarterOfYear([OrderDate]))

Explanation: 

The Date.QuarterOfYear() function returns the number of the quarter (1-4) from a date, while the Number.ToText() function converts a number to text format. 

1. Notice that a new column Quarter was added.

1. Click File > Close & Load to refresh your worksheet.

What’s next?

We’ve covered the basics of how you can use Excel Power Query to get, transform, and load data in Excel. We hope this tutorial has given you a great starting point working with Excel Power Query. Be sure to continue learning about Excel’s Data Model and Power Pivot if you want to master Business Intelligence using Excel.

In addition to importing your data into Excel, take a look at Coupler.io. This excellent integration tool may be a great solution you need if your data source is not among those currently available in Power Query. With this tool, you can import data from different apps into Excel — no coding required. You can also automate the import process on the schedule you want! 

Back to Blog