Power Query is a powerful data connection, transformation, and automation tool available in Excel. It simplifies the process of importing, cleaning, and transforming data, helping users efficiently manage large datasets from multiple sources. Whether you’re working with messy data or combining multiple datasets, Power Query can streamline your workflow. In this beginner’s guide, you’ll learn how to get started with Power Query, enabling you to perform powerful data operations with ease.

Required Tools

The tutorial is specifically made for any version of Microsoft Excel. The same steps should work with Google Sheets with some minor changes.

What is Power Query?

Power Query is a feature in Excel that allows you to:

• Connect to multiple data sources: Excel files, CSV, databases, websites, and more.
• Clean and transform data: Remove duplicates, split columns, filter data, replace values, and more.
• Automate repetitive tasks: Save transformations and apply them to updated data automatically.

It is especially useful for tasks like data cleaning, combining datasets, and preparing data for analysis.

How to Enable Power Query in Excel

If you are using Excel 2016 or later, Power Query is already built into the Data tab under the Get & Transform Data group. However, for Excel 2010 or 2013 users, it needs to be downloaded as a separate add-in.

For Excel 2016 and Later:

• Go to the Data tab in Excel.
• In the Get & Transform Data group, you’ll see options such as From Table/Range, From Web, and From File.

For Excel 2010 or 2013:

• Download the Power Query add-in from Microsoft’s website.
• Once installed, it will appear as a separate tab named Power Query on the Excel ribbon.

Getting Started with Power Query

Step 1: Importing Data with Power Query

The first step in using Power Query is importing data from various sources. It supports a wide range of data sources, including Excel files, CSV files, databases (SQL, Access), and even web pages.

Importing Data from an Excel File:

• Go to the Data tab in Excel.
• Click on Get Data and select From File > From Workbook.
• Browse and select the Excel file you want to import.
• After selecting the file, it will show a preview of the data in a new window.
• Click Load to load the data into Excel, or click Transform Data to clean or modify the data before loading.

Importing Data from a Web Page:

• Go to Data > Get Data > From Web.
• Enter the URL of the web page that contains the data you want to import.
• Power Query will extract data tables from the web page, and you can select the one you want to work with.

Step 2: Navigating the Power Query Editor

After importing data, you’ll enter the Power Query Editor, where you can clean, transform, and shape the data.

Query Editor Interface:

• Ribbon: The top of the window contains commonly used transformation tools (e.g., remove duplicates, split columns).
• Queries Pane: The pane that shows all the queries you’ve created.
• Data Preview: The center pane shows a preview of your data.
• Applied Steps: The pane that lists each step that has been applied to the data, allowing you to track and undo changes.

Step 3: Basic Data Transformation

In Power Query, you can perform several transformations to clean and prepare your data for analysis.

• Remove Unnecessary Columns: In the editor, select any columns you don’t need, right-click, and choose Remove.
• Filter Rows: To filter data, click the dropdown arrow in a column header, select the filter criteria (e.g., greater than, contains), and apply the filter.
• Split Columns: You can split data in a column into multiple columns. For example, to split a “Full Name” column into “First Name” and “Last Name,” go to the Transform tab, click Split Column, and choose how you want to split the data (by delimiter, number of characters, etc.).
• Remove Duplicates: If your data contains duplicates, you can easily remove them by selecting the column and clicking Remove Duplicates in the Home tab.

Step 4: Combining Data from Multiple Sources

Power Query allows you to combine data from different files or sources using Append and Merge functions.

Append Queries:

• This function stacks data from multiple tables or files. It’s useful when you want to combine datasets with the same structure (e.g., monthly sales reports).
• Go to Home > Append Queries, select the queries (datasets) to combine, and Power Query will merge them into one table.

Merge Queries:

• Use this function to combine datasets based on a common column (like a VLOOKUP). For instance, you can combine customer data with sales data using a common Customer ID.
• Go to Home > Merge Queries, select the datasets to merge, and choose the common column that links the two tables.

Step 5: Loading Data Back into Excel

After performing all the necessary transformations, the next step is to load the cleaned data back into Excel.

Load Data into a Table:

• Once your query is ready, click Close & Load in the top-left corner of the editor.
• This will load your data into a new worksheet in Excel as a table, ready for analysis.

Load to a Pivot Table:

• Alternatively, you can load your transformed data directly into a PivotTable for more advanced data analysis.
• Click the dropdown next to Close & Load, choose Load To, and select PivotTable Report.

Automating Data Refresh with Power Query

One of the major benefits of Power Query is that it allows you to set up automated data refreshes. Once you’ve established a query, you don’t need to go through the entire process again when the data is updated.

To refresh your data:

• Simply right-click on the table or PivotTable linked to the query and select Refresh.
• It will automatically pull in new data from the source, apply all the transformations you previously set, and update the table.

This automation feature makes Power Query an invaluable tool for handling recurring reports and dashboards.

Conclusion

Power Query is an incredibly powerful tool for anyone working with data in Excel. From importing and cleaning data to merging datasets and automating refreshes, it simplifies complex tasks and saves time. Once you get familiar with its capabilities, you’ll find yourself using it for all your data preparation needs. Start with basic transformations, and as you grow more comfortable, explore advanced features like custom columns and M language.

So this was the beginner’s guide to Power Query from Import Range. If you have any questions or need help getting started, feel free to ask in the comments or reach out via the contact page!