Power Query – Importing and Managing Data: Difference between revisions

From Training Material
Jump to navigation Jump to search
← Older editNewer edit →
No edit summary
No edit summary
Line 2: Line 2:


== Objective ==
== Objective ==
In this module, you'll learn how to import data from various sources and manage basic data loading settings in Power Query.
Learn how to import data from different sources and manage query settings.


== Files Used ==
== Files Used ==
Line 12: Line 12:
== Importing Data from Excel ==
== Importing Data from Excel ==
'''Steps:'''
'''Steps:'''
# Open Excel
# Go to the '''Data''' tab → '''Get Data''' → '''From File''' → '''From Workbook...'''
# Go to the '''Data''' tab → '''Get Data''' → '''From File''' → '''From Workbook...'''
# Select '''Sales_January.xlsx''' and click '''Import'''
# Select '''Sales_January.xlsx'''
# In the Navigator window, select the sheet and click '''Transform Data'''
# In the Navigator window, select the sheet and click '''Transform Data'''
# The data will open in Power Query Editor


== Importing Data from CSV ==
== Importing Data from CSV ==
Line 22: Line 20:
# Go to the '''Data''' tab → '''Get Data''' → '''From File''' → '''From Text/CSV'''
# Go to the '''Data''' tab → '''Get Data''' → '''From File''' → '''From Text/CSV'''
# Select '''Employees.csv'''
# Select '''Employees.csv'''
# Review the preview window, then click '''Transform Data''' to load it into Power Query
# Click '''Transform Data'''
 
== Importing from a Text File ==
'''Steps:'''
# Go to the '''Data''' tab → '''Get Data''' → '''From File''' → '''From Text/CSV'''
# Select '''ProductList.txt'''
# Set delimiter to '''Tab'''
# Click '''Transform Data'''


== Importing Multiple Excel Files ==
== Importing Multiple Excel Files ==
'''Scenario:''' You want to combine all monthly sales files into a single dataset.
'''Scenario:''' Combine monthly files into one dataset.


'''Steps:'''
'''Steps:'''
# Place all monthly files (e.g. Sales_January.xlsx, Sales_February.xlsx) in one folder
# Place '''Sales_January.xlsx''' and '''Sales_February.xlsx''' in the same folder
# Go to the '''Data''' tab → '''Get Data''' → '''From File''' → '''From Folder'''
# Go to the '''Data''' tab → '''Get Data''' → '''From File''' → '''From Folder'''
# Browse to the folder containing the Excel files
# Select the folder and click '''Transform Data'''
# Click '''Transform Data'''
# In Power Query Editor: click '''Combine''' → '''Combine & Transform Data'''
# In Power Query Editor:
# Select the worksheet to use and confirm
#* Click '''Combine''' → '''Combine & Transform Data'''
 
#* Select the sheet to combine from each file
== Managing Queries ==
# Power Query will generate a sample query and combine all files into one table
* Rename queries (right-click → '''Rename''')
* Disable load to sheet (right-click → uncheck '''Enable Load''')
* View query dependencies: '''View''' tab → '''Query Dependencies'''
 
== Additional Exercise: Import Multiple CSV Files ==
'''Goal:''' Practice folder import using CSVs.


== Importing from a Text File ==
'''Steps:'''
'''Steps:'''
# Go to the '''Data''' tab → '''Get Data''' → '''From File''' → '''From Text/CSV'''
# Save a few copies of '''Employees.csv''' with small changes (e.g. Employees_1.csv, Employees_2.csv)
# Select the file '''ProductList.txt'''
# Place them in a folder
# Choose delimiter: ''Tab''
# Use '''Get Data → From Folder''' to combine them
# Click '''Transform Data''' to edit it in Power Query
# Filter only Active employees
# Load to a worksheet


== Managing Queries ==
== Extension: Create a Parameter for Folder Path ==
In Power Query Editor, you can:
'''Goal:''' Make your folder import dynamic.
* Rename queries (right-click query name → '''Rename''')
* Disable load (right-click query → uncheck '''Enable Load to Worksheet''')
* View query dependencies: go to the '''View''' tab → click '''Query Dependencies'''


== Merging Queries (Preview) ==
'''Steps:'''
You’ll later learn how to join data sources together using merges and appends. 
# In Power Query Editor → '''Manage Parameters''' → '''New Parameter'''
For now, make sure you have both Sales and Employees queries loaded.
# Name: `ImportFolder`, Type: Text
# Use this parameter in the Folder source dialog
# Tip: open Advanced Editor and replace the static path with `ImportFolder`


----
----

Revision as of 09:25, 19 June 2025

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)
  • Disable load to sheet (right-click → uncheck Enable Load)
  • View query dependencies: View tab → Query Dependencies

Additional Exercise: 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

Extension: Create a Parameter for Folder Path

Goal: Make your folder import dynamic.

Steps:

  1. In Power Query Editor → Manage Parameters → New Parameter
  2. Name: `ImportFolder`, Type: Text
  3. Use this parameter in the Folder source dialog
  4. Tip: open Advanced Editor and replace the static path with `ImportFolder`

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=88887"