Importing Data from Excel and CSV into Power Query

What is Power Query and Why Should Managers Care?

Before we dive into the "how," it's important to establish the "why." Your audience of managers will be most receptive if they understand the value proposition immediately. Power Query is not just another feature; it's a fundamental shift in how they can work with data.

At its core, Power Query is what's known as an ETL (Extract, Transform, Load) tool built directly into Excel. You can explain this to your audience with a simple analogy:

• Extract: It pulls data from various sources without you needing to copy-paste.
• Transform: It records your cleaning steps (which we'll cover in the next lessons).
• Load: It places the clean data into a new sheet or data model for analysis.

The magic is that this entire process can be "refreshed" with a single click when new data arrives.

To help you frame this for your workshop, the following video provides an excellent real-world problem and contrasts the painful manual approach with the elegant Power Query solution.

Power Query is located in the Data tab in Excel, within a group called Get & Transform Data. This is the command center for all data import operations.

Method 1: Importing from an Excel Workbook

This is one of the most common tasks. You'll often need to connect to sales reports, inventory lists, or HR data stored in Excel files. There are two main scenarios: the data is inside your current workbook, or it's in a separate file.

A. Data from the Current Workbook (From Table/Range)

If the data you want to analyze is already in a sheet in your active Excel file, the best practice is to first convert it into an Official Excel Table. A table gives your data structure, which Power Query works with best.

To create a table, simply click anywhere inside your data range and press Ctrl+T.

Once your data is in a table, you can pull it into Power Query.

B. Data from an External Excel Workbook

More often, the data will be in a separate file that you receive regularly (e.g., a monthly sales export from another department). The goal is to create a live connection to that file.

The process is straightforward: Data > Get Data > From File > From Excel Workbook. This opens a browser window for you to select the file. After selecting it, the Navigator window appears, allowing you to choose which specific sheet or table within that workbook you want to import.

The following video provides a clear, step-by-step demonstration.

For a text-based alternative that you can use as a handout or reference, this guide provides clear screenshots of the process.

Method 2: Importing from a CSV or Text File

CSV (Comma-Separated Values) files are another extremely common format for raw data exports from business systems (like ERPs, CRMs, or databases). A CSV is a simple text file where columns are separated by a delimiter, usually a comma.

The import process is nearly identical to the one for Excel files: Data > Get Data > From File > From Text/CSV.

When you import a CSV, Power Query will show you a preview and automatically try to detect the delimiter (e.g., comma, tab, semicolon) and the data types. Usually, the default settings are correct. As before, you will choose Transform Data to proceed to the Power Query Editor.

The Power User Method: Importing All Files from a Folder

This is a technique that will be a game-changer for the managers in your workshop. Often, they don't just have one file; they have a folder full of them—for example, a separate sales report for January, February, and March. Manually combining these is tedious. Power Query can do it automatically.

The process is: Data > Get Data > From File > From Folder.

You simply point Power Query to the folder, and it will list all the files inside. You can then click Combine & Transform Data. Power Query will use the first file as a template and then automatically append all the other files in the folder into a single, unified table.

This video demonstrates the power of combining multiple CSV files.

The best part? If a new file (e.g., April's sales report) is added to the folder later, the manager only needs to click "Refresh," and the new data is automatically cleaned and added to their report. This reinforces the core benefit of setting up a process once and reusing it effortlessly.

Conclusion

In this lesson, you've learned the essential first step of any automated data workflow: importing data into Power Query. You are now equipped to teach your workshop audience how to connect to data from single Excel and CSV files, as well as how to efficiently combine multiple files from a folder.

Key Takeaways for Your Workshop:

• Power Query is Excel's engine for automating data import and preparation, found under the Data > Get & Transform Data tab.
• You can create refreshable connections to external Excel workbooks and CSV files, eliminating the need for copy-pasting.
• For data already inside your workbook, convert it to a Table (Ctrl+T) and use the From Table/Range command.
• The From Folder option is a powerful tool for automatically combining many files that share the same layout.

Preview of the Next Lesson:
Now that we know how to get data into Power Query, our next step is to explore the environment where the "transformation" happens. In the next lesson, we will navigate the Power Query interface and understand the 'applied steps' concept. This is where you'll learn how Power Query records your actions to create a repeatable cleaning process.