Power Query – Data Transformation: 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 clean and reshape data in Power Query using built-in transformation tools.
Clean and reshape data using Power Query’s built-in tools.


== Files Used ==
== Files Used ==
Line 10: Line 10:
== Removing Rows and Columns ==
== Removing Rows and Columns ==
'''Steps:'''
'''Steps:'''
# Import the file '''Survey_Results.xlsx'''
# Load '''Survey_Results.xlsx'''
# In Power Query Editor:
# In Power Query Editor:
#* Go to the '''Home''' tab → '''Remove Rows''' → '''Remove Blank Rows'''
#* Go to the '''Home''' tab → '''Remove Rows''' → '''Remove Blank Rows'''
#* Select any unnecessary columns → Right-click → '''Remove Columns'''
#* Select columns to remove → Right-click → '''Remove Columns'''


== Changing Data Types ==
== Changing Data Types ==
'''Steps:'''
'''Steps:'''
# Click the icon next to a column header (e.g. ABC123)
# Click the icon next to a column header (e.g. ABC123)
# Choose appropriate type (e.g. '''Whole Number''', '''Date''', '''Text''')
# Choose data type (e.g. '''Whole Number''', '''Date''', '''Text''')
# Or: go to the '''Transform''' tab → '''Data Type'''
# Or: '''Transform''' tab → '''Data Type'''


== Renaming Columns ==
== Renaming Columns ==
'''Steps:'''
'''Steps:'''
# Double-click the column header, or
# Double-click the column name or
# Right-click → '''Rename''' → Type the new name
# Right-click → '''Rename''' → Type new name


== Filtering Data ==
== Filtering and Sorting ==
'''Steps:'''
'''Steps:'''
# Click the filter icon in any column header
# Click filter icon in column header
# Use checkboxes, number filters, or text search to apply filters
# Use built-in filters or checkboxes
 
# Click arrow again to access '''Sort Ascending''' / '''Sort Descending'''
== Sorting Data ==
'''Steps:'''
# Click the dropdown arrow on any column
# Choose '''Sort Ascending''' or '''Sort Descending'''


== Grouping Data ==
== Grouping Data ==
'''Steps:'''
'''Steps:'''
# Go to the '''Transform''' tab → Click '''Group By'''
# Select a column
# Choose the column to group on (e.g. Product or Region)
# Go to '''Transform''' tab → '''Group By'''
# Choose an aggregation (e.g. Sum, Count, Average)
# Choose operation: Sum, Count, etc.


== Splitting Columns ==
== Splitting Columns ==
'''Steps:'''
'''Steps:'''
# Select a column with combined values (e.g. full name or product code)
# Select a column (e.g. Full Name)
# Go to the '''Home''' tab → '''Split Column'''
# Go to '''Home''' tab → '''Split Column'''
# Choose method: by delimiter, number of characters, etc.
# Choose method (e.g. by delimiter)
 
== Unpivoting Data ==
'''Use case:''' When you have month names as column headers and want to convert them into rows.


== Unpivoting Columns ==
'''Steps:'''
'''Steps:'''
# Import the file '''Orders_2023.xlsx'''
# Load '''Orders_2023.xlsx'''
# Select the columns with month names
# Select month columns (e.g. Jan, Feb)
# Go to the '''Transform''' tab → '''Unpivot Columns'''
# Go to '''Transform''' tab → '''Unpivot Columns'''


== Reordering Columns ==
== Reordering Columns ==
'''Steps:'''
'''Steps:'''
# Drag and drop columns directly in the preview grid, or
# Drag columns to new position or
# Right-click a column → '''Move''' → choose position (Left, Right, Beginning, End)
# Right-click column → '''Move''' → choose direction


== Duplicating and Referencing Queries ==
== Duplicating and Referencing Queries ==
'''Steps:'''
'''Steps:'''
# In the Queries pane, right-click a query
# Right-click a query in the left pane
# Choose:
# Choose:
#* '''Duplicate''' – makes a copy of the query with independent steps
#* '''Duplicate''' – creates a full copy
#* '''Reference''' – creates a new query that uses the output of the original
#* '''Reference''' – creates a linked copy
 
== Additional Exercise: Cleanup and Pivot Prep ==
'''Goal:''' Prepare survey data for analysis.
 
'''Steps:'''
# Remove rows with missing satisfaction scores
# Change column names to lowercase
# Group by satisfaction rating and count rows
# Load results into a new sheet
 
== Extension: Conditional Columns ==
'''Goal:''' Create a new column that classifies satisfaction as Positive/Neutral/Negative.
 
'''Steps:'''
# Go to the '''Add Column''' tab → '''Conditional Column'''
# Name: `SatisfactionLevel`
# Logic:
#* If Satisfaction = 4 or 5 → "Positive"
#* If Satisfaction = 3 → "Neutral"
#* If Satisfaction = 1 or 2 → "Negative"
# Click OK


----
----


→ Continue to [[Power Query – Automation and Parameters|Module 4: Automation and Parameters]]
→ Continue to [[Power Query – Automation and Parameters|Module 4: Automation and Parameters]]

Revision as of 09:45, 19 June 2025

Module 3: Data Transformation Techniques

Objective

Clean and reshape data using Power Query’s built-in tools.

Files Used

Removing Rows and Columns

Steps:

  1. Load Survey_Results.xlsx
  2. In Power Query Editor:
    • Go to the Home tab → Remove Rows → Remove Blank Rows
    • Select columns to remove → Right-click → Remove Columns

Changing Data Types

Steps:

  1. Click the icon next to a column header (e.g. ABC123)
  2. Choose data type (e.g. Whole Number, Date, Text)
  3. Or: Transform tab → Data Type

Renaming Columns

Steps:

  1. Double-click the column name or
  2. Right-click → Rename → Type new name

Filtering and Sorting

Steps:

  1. Click filter icon in column header
  2. Use built-in filters or checkboxes
  3. Click arrow again to access Sort Ascending / Sort Descending

Grouping Data

Steps:

  1. Select a column
  2. Go to Transform tab → Group By
  3. Choose operation: Sum, Count, etc.

Splitting Columns

Steps:

  1. Select a column (e.g. Full Name)
  2. Go to Home tab → Split Column
  3. Choose method (e.g. by delimiter)

Unpivoting Columns

Steps:

  1. Load Orders_2023.xlsx
  2. Select month columns (e.g. Jan, Feb)
  3. Go to Transform tab → Unpivot Columns

Reordering Columns

Steps:

  1. Drag columns to new position or
  2. Right-click column → Move → choose direction

Duplicating and Referencing Queries

Steps:

  1. Right-click a query in the left pane
  2. Choose:
    • Duplicate – creates a full copy
    • Reference – creates a linked copy

Additional Exercise: Cleanup and Pivot Prep

Goal: Prepare survey data for analysis.

Steps:

  1. Remove rows with missing satisfaction scores
  2. Change column names to lowercase
  3. Group by satisfaction rating and count rows
  4. Load results into a new sheet

Extension: Conditional Columns

Goal: Create a new column that classifies satisfaction as Positive/Neutral/Negative.

Steps:

  1. Go to the Add Column tab → Conditional Column
  2. Name: `SatisfactionLevel`
  3. Logic:
    • If Satisfaction = 4 or 5 → "Positive"
    • If Satisfaction = 3 → "Neutral"
    • If Satisfaction = 1 or 2 → "Negative"
  4. Click OK

→ Continue to Module 4: Automation and Parameters


Return to Main Page

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