Power Query – Importing and Managing Data

Module 2: Importing and Managing Data

Objective

Learn how to import data from different sources and manage query settings.

Files Used

• Download Sales_January.xlsx
• Download Sales_February.xlsx
• Download Employees.csv
• Download ProductList.txt

Importing Data from Excel

Steps:

1. Go to the Data tab → Get Data → From File → From Workbook...
2. Select Sales_January.xlsx
3. In the Navigator window, select the sheet and click Transform Data

Importing Data from CSV

Steps:

1. Go to the Data tab → Get Data → From File → From Text/CSV
2. Select Employees.csv
3. Click Transform Data

Importing from a Text File

Steps:

1. Go to the Data tab → Get Data → From File → From Text/CSV
2. Select ProductList.txt
3. Set delimiter to Tab
4. Click Transform Data

Importing Multiple Excel Files

Scenario: Combine monthly files into one dataset.

Steps:

1. Place Sales_January.xlsx and Sales_February.xlsx in the same folder
2. Go to the Data tab → Get Data → From File → From Folder
3. Select the folder and click Transform Data
4. In Power Query Editor: click Combine → Combine & Transform Data
5. Select the worksheet to use and confirm

Managing Queries

• Rename queries (right-click → Rename)
• Duplicate queries (right-click → Duplicate)
• View query dependencies: View tab → Query Dependencies

Import Multiple CSV Files

Goal: Practice folder import using CSVs.

Steps:

1. Save a few copies of Employees.csv with small changes (e.g. Employees_1.csv, Employees_2.csv)
2. Place them in a folder
3. Use Get Data → From Folder to combine them
4. Filter only Active employees
5. Load to a worksheet

Create a Parameter for Folder Path

Goal: Make your folder import dynamic so the query works even if the folder path changes.

1. Open Power Query Editor

If you're not already there:

• In Excel → Data tab → Queries & Connections
• Right-click your folder-based query → Edit

2. Create the parameter

• In Power Query Editor → Home tab → Manage Parameters → New Parameter
• Fill in:
  • Name: `ImportFolder`
  • Type: Text
  • Current Value: (paste the folder path you used originally)
• Click OK

3. Replace the static path with the parameter

Option A: Source step

1. In the Applied Steps pane → click ⚙ next to Source
2. Replace the folder path with: `ImportFolder`
3. Click OK

Option B: Advanced Editor

1. Go to Home tab → Advanced Editor
2. Find the line:

Source = Folder.Files("C:\your\original\path")

1. Replace with:

Source = Folder.Files(ImportFolder)

1. Click Done

4. Load the result

• Go to Home → Close & Load To...
• Choose whether to create a table, connection, or add to the Data Model

✅ Result

You can now change the folder path in the parameter settings, and the query will update automatically.

---

Return to Main Page

→ Continue to Module 3: Data Transformation Techniques

• Module 1: Introduction to Power Query
• Module 2: Importing and Managing Data
• Module 3: Data Transformation Techniques
• Module 4: Automation and Parameters
• Module 5: Power Pivot Overview
• Module 6: Summary and Final Exercises