Power Query – Importing and Managing Data

From Training Material
Jump to navigation Jump to search

Module 2: Importing and Managing Data

Objective

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

Files Used

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

Retrieved from "https://training-course-material.com/index.php?title=Power_Query_–_Importing_and_Managing_Data&oldid=88908"