Introduction
In our last lesson, we focused on a crucial principle of professional spreadsheet design: creating reusable templates by separating inputs, calculations, and outputs. This structure makes your work clear, robust, and easy for clients to use. Now, we will address the next logical question: how do we get data into these templates efficiently and automatically?
This lesson introduces Power Query, one of the most powerful features in Excel for freelancers and analysts. Think of it as your automated data assistant. It can connect to a huge variety of data sources, clean up messy information, and prepare it for analysis—all in a repeatable sequence of steps. Mastering Power Query is a significant step toward automating your workflows and delivering high-value solutions.
Today, you will learn how to launch Power Query and get comfortable with its environment. Specifically, we will connect to three of the most common data sources you'll encounter: an Excel workbook, a web page, and a CSV file.
1. What is Power Query?
Before we dive in, let's establish a clear understanding of what Power Query is and why it's so important. In short, Power Query is a data connection and preparation tool. Its job is to handle the entire process of Extracting data from a source, Transforming it into a clean and usable format, and Loading it into Excel for analysis. This is often abbreviated as ETL.
By creating a "query," you are essentially recording a set of steps to be performed on your data. The magic is that you can simply "refresh" this query later, and Excel will automatically repeat the entire sequence on any new data from the source. This saves you from the tedious, error-prone work of manually copying, pasting, and cleaning data every time a report needs to be updated.
Power Query Tutorial - What is it, How to use, Full ...
This article from Chandoo.org provides a concise introduction to Power Query's purpose and benefits.
Please read the first two sections of the article, from the start down to the image of the Power Query window. Focus on the sections titled What is Power Query? and the list of what it is used for. This will solidify your understanding of its role in the data analysis process.
2. Accessing Power Query and Navigating the Editor
Power Query is built directly into the modern versions of Excel. You don't need to install anything extra. The primary entry point is the Data tab on the Excel ribbon, within the Get & Transform Data group.
When you connect to a data source and choose to edit it, you will enter the Power Query Editor. This is a separate window where all the data transformation magic happens. It's essential to familiarize yourself with its layout.
The editor has a few main components, as labeled in the image above:
- Ribbon: Contains all the tools and commands for transforming your data, organized into tabs like Home, Transform, and Add Column.
- Query Pane: Lists all the queries (data connections) in your current workbook.
- Data Preview: Shows a sample of your data as it looks after the currently selected step. This is where you see the live effect of your transformations.
- Applied Steps: This is a critical pane. It records every single action you take as a separate, named step. You can select previous steps to see what the data looked like at that stage, modify step settings, or delete steps.
- Formula Bar: Displays the underlying code (written in a language called "M") for the selected step. You don't need to write this code yourself, but it's useful to see how Power Query works behind the scenes.
To see a live walkthrough of this environment, the following video provides an excellent tour.
Power Query - Beginner to PRO Masterclass in 30 minutes
This video from Chandoo provides a clear, guided tour of the Power Query Editor's interface.
Please watch the segment from "what power query is like". The presenter will walk you through the four main areas of the screen, explaining the purpose of the ribbon, query list, data preview, and the all-important "Applied Steps" pane.
3. Practical Examples: Connecting to Data Sources
The best way to learn is by doing. Let's walk through connecting to the three most common types of data sources you'll encounter as a freelancer.
A. Connecting to an Excel Workbook
Often, the data you need will be in another Excel file. Power Query allows you to create a live link to that file, so you don't have to copy-paste data manually.
Learn to Automate Everything with Power Query in Excel (Download Files)
In this video, Leila Gharani demonstrates how to connect to data in an external Excel workbook. This is a fundamental skill.
Watch from Data > Get Data. Observe the process: Navigating to Data > Get Data > From File > From Excel Workbook. Selecting the file to import. Using the Navigator window to preview and select the specific sheet you want to load. Clicking Transform Data to open the Power Query Editor.
B. Connecting to a Web Page
One of Power Query's most impressive features is its ability to extract data directly from web pages. This is perfect for pulling data from public sources like Wikipedia, government websites, or financial portals.
Power Query - Beginner to PRO Masterclass in 30 minutes
Let's return to the Chandoo video to see how to scrape a table of data from a Wikipedia page.
Watch the section from "2020 Summer Olympics". You'll see how to use the From Web connector, paste a URL, and then use the Navigator to select the specific HTML table you want from the page. This is a powerful technique for acquiring data you don't have locally.
C. Connecting to a CSV File
CSV (Comma-Separated Values) is a universal format for raw data. You will frequently receive data from clients or download it from systems in this format. Power Query can not only connect to a single CSV file but can also intelligently combine all CSV files within a single folder.
Learn to Automate Everything with Power Query in Excel (Download Files)
This clip from Leila Gharani's video shows an incredibly useful, real-world scenario: combining data from multiple sales files stored in a folder.
Watch from Data > Get Data > From File > From Folder. The key steps are: Choosing the From Folder connector. Pointing Power Query to the folder containing your files. Using the Combine & Transform Data option, which allows Power Query to open each file, apply the same logic, and stack them into a single, unified table.
This "From Folder" method is exceptionally powerful. If a client sends you a new sales file each month, you can simply drop it into the folder and refresh your query—all the new data will be automatically combined and cleaned without any extra work.
Conclusion
In this lesson, you took your first and most important step into the world of data automation with Power Query. You've moved beyond the spreadsheet grid to the powerful engine that feeds it. Building on our previous lesson about structuring workbooks, you now have the foundational skill for getting data into those structures in a professional, repeatable way.
Key Takeaways:
- Power Query is for ETL: Its purpose is to Extract, Transform, and Load data, automating the entire data preparation pipeline.
- The Gateway is the Data Tab: You access all Power Query features from the "Get & Transform Data" group on the Data ribbon.
- The Editor is Your Workshop: The Power Query Editor is where you clean and shape your data. Its key components are the Ribbon, Query Pane, Data Preview, and the crucial Applied Steps pane.
- Connect to Anything: You've seen how to create live, refreshable connections to Excel workbooks, web pages, and folders of CSV files—three of the most common data sources you'll ever need.
In our next lesson, we will stay inside the Power Query Editor and focus on the "T" in ETL: Transform. You will learn how to apply common transformation steps like filtering rows, removing columns, and changing data types to clean the raw data you've just connected to.