- What is Excel Power Query?
- How to use Excel Power Query?
- How to automate Excel data with Layer?
Knowing your way around Excel formulae is a helpful skill, especially if you work with large datasets and multiple worksheets at once. In another article, we showed you the two basic procedures to transfer data from one Excel worksheet to another automatically, as a simple alternative to manual copy/paste. Although the Copy and Paste Link and Worksheet Reference functions can make your life easier, Excel has integrated features that require even less effort.
In this article, we’ll show you how to use Power Query in Excel to import data from various sources, combine and merge tables according to your preferences; all without having to learn any specific code.
What is Excel Power Query?
Power Query is a Microsoft Excel tool that simplifies the two main tasks of any spreadsheet user: importing and sorting data. Before showing all the applications, let’s briefly explore how they work.
How do Power Queries work in Excel?
Power Queries are commands set up in Excel that can be broken down into the following steps:
- Import or connection: You can connect data stored in the cloud, specific service, or local environment. Moreover, you can connect from multiple sources to combine all of your data in one place.
- Editing or transformation: You can change the data according to your preferences, as these will not affect the original source.
- Automate or load: After completing your query, you can then load it into a worksheet or Data Model and update it regularly, without the need to repeat that same process.
So, is Power Query easy to use? Yes. Power Query doesn’t require knowledge of coding or programming and since 2016, it has become an integrated tool in Excel, so there is no longer a need for third-party installation. Like any automation tool, its most attractive feature is the ability to achieve the same results in half the time.
How to use Excel Power Query?
Before showing how you can use Power Query in Excel, note that it only works in the following Excel versions:
- Power Query for Excel 2010 and 2013: To use it, you need this Microsoft add-in. Unfortunately, there are no add-ins available for macOS and you can only refresh the existing query, but not create a new one or edit.
- Power Query for Excel 2016, 2019, or 365: Depending on the version, Power Query will appear under as “Get Data & Transform” or “Get Data” button. This integrated feature allows you to create and edit queries in Windows and Mac.
This article will show you how to use Power Query for Excel 365 on Mac.
How to import data using Excel Power Query?
Importing your data using Power Query is simple, as Excel provides a great variety of data connections that are accessible from your toolbar. Now that you know what Power Query is and the type of data format it can import, let’s look at how you can import data from an Excel spreadsheet.
Discover what Excel version control is, the version control features Excel has to offer, and how to use them to share, merge, and review Excel changesREAD MORE
How to import data from a single sheet in Excel Power Query?
Imagine that you want to analyze the car sales made by an employee in 2021. Using Power Query, you can import the data without affecting the source file at all.
- 1. Open a new Excel spreadsheet and go to Data > Get Data (Power Query). As explained, there are several options depending on the source type. Here, I will select “Get Data (Power Query)” to import a spreadsheet stored locally.
- 2. Select “Excel workbook”.
- 3. Once you have selected your preferred data source, choose your “Connection settings”. Here, I can select the local file by clicking on “Browse”.
- 4. For a quicker search, type the source filename in the search bar. Here, I’ve typed ‘SALES’. Select the file, click “Get Data”, and then the “Next” button located on the bottom right-hand corner of the original window.
- 5. You should now be able to see the data preview window. If you are using Windows, then you can transform or edit your data at this point. However, Mac will only allow you to load data and then apply any changes needed.
Once you load the data, Excel automatically saves the query in your system. Every time you want to import or format (for Windows), click the “Refresh” icon in the top-right corner to repeat the steps. This will eliminate repetitive manual work and unnecessary human errors.
How to import data from multiple sheets in Excel Power Query?
As mentioned before, Power Query can import data from multiple files at the same time. This is very useful when you don’t want to import all the data from a larger file, and would like to import specific sheets. Here is how you can get data from multiple sheets.
- 1. Repeat steps 1-3 from the previous section.
- 2. Search for your general folder rather than a specific sheet. Here, I’ll search for my ‘SALES2021’ file. Click on “Get Data” and then “Next” located in the bottom right-hand corner.
- 3. As you can see, you can now select the specific sheets of data you’d like to import. I have selected two sheets corresponding to the sales employees I want to compare. Once selected, click “Load”.
- 4. You should now have a new Excel workbook with the two loaded sheets. Below is an example of how Excel has converted my data into a table, each in a separate tab.
In Windows, Power Query also gives you the option to load to a pivot table, pivot chart or simply create a connection for the query. This last option does not import data, but it’s still a useful way of speeding up any data transformation process.
Now that you know how to load data from a single sheet and multiple sheets, let’s see how to edit the query in Excel.
How to Combine Multiple Excel Files Into One
Discover the most popular methods used to manually or automatically combine multiple Excel spreadsheets and data inputs into one master fileREAD MORE
How to edit External Data Range Properties in Excel?
A great way to edit future imports using Power Query is to change the settings for the external data range.
- 1. Go to Data > Properties to edit any of the following elements.
Below are summaries of the three most useful properties to consider for future queries:
- Query Definition: Leave the “Save query definition” box ticked and change the name to something that will be easier to remember. Don’t forget that the point of Power Query is to reduce the number of steps in data transformation processes.
- Refresh Control: What’s most important here is the “Refresh data when opening the file” option. If you choose this option, then any change you make to the original file will automatically affect the destination file.
- Data Formatting and Layout: If you select “Insert cells for new data, delete unused cells”, any new rows of data added to your source file will be transferred and formatted to coincide with the rest of your Power Query table, and any unused cells will be deleted.
It’s crucial to understand how Excel can transform your data automatically, to boost productivity. Although the most basic features of Power Query in Excel work in a similar way in Windows and Mac, further automation features may vary to a greater extent.
To avoid version incompatibility and create your own automated spreadsheet workflow regardless of your operating system, check out the benefits of using Layer as an alternative to Excel Power Query.
How to remove a connection in Excel?
If you would like to remove a Power Query connection in Excel, follow the steps below:
- 1. Go to Data > Queries & Connections.
- 2. Click on the query you’d like to delete and select “Remove”.
- 3. Excel will prompt you with an alert message to make sure you wish to proceed.
If you do not use a workflow automation tool, Power Query is a great way to not only automate updates, edits, or repetitive tasks but also to track any modifications made to the file.
How to automate Excel data with Layer?
Layer is a spreadsheet platform that works on top of Excel and Google Sheets. It allows you to easily manage and automate spreadsheet workflows. Using Layer, you can:
- Upload or connect your existing Excel or Google Sheets-based budget.
- Share different sheets or even cell ranges of your spreadsheet with various stakeholders or departments involved in the budgeting process.
- Automate your communication flows and keep track of your data submissions, contributors, and deadlines.
- Review every single change made and decide which ones to merge with your spreadsheet or discard.
- Eliminate errors in your budget or manually copying and pasting data across files.
In this article, we have shown you how to use Excel Power Query at a basic level in Excel 2016, 2019, and Office 365 for Mac and Windows. It’s a powerful query tool that can import a variety of data sources in terms of file type and storage systems. In a few simple steps, you can automate your most common repetitive tasks, avoid future manual errors, and increase the efficiency of your business.
If you want to learn more advanced methods to automate your workflow in Excel, read our article on How To Use Macros in Excel To Automate Tasks.