Power Query – Introduction: Difference between revisions

From Training Material
Jump to navigation Jump to search
← Older edit
No edit summary
 
(2 intermediate revisions by the same user not shown)
Line 1: Line 1:
= Module 1: Introduction to Power Query =
= Module 1: Introduction to Power Query =
== Objective ==
Get familiar with the Power Query interface and understand what it can do.
== Files Used ==
* [[Media:Sales_January.xlsx|Download Sales_January.xlsx]]


== What is Power Query? ==
== What is Power Query? ==
Line 6: Line 12:


You can find Power Query in Excel 2016 and later, under the '''Data''' tab in the section called '''Get & Transform Data'''.
You can find Power Query in Excel 2016 and later, under the '''Data''' tab in the section called '''Get & Transform Data'''.
== Why Use Power Query? ==
* Automates repetitive data-cleaning tasks
* Simplifies combining data from various sources
* Eliminates the need for manual copy-pasting or complex formulas
* Keeps a full record of all applied steps for transparency and easy modification


== Where to Find It ==
== Where to Find It ==
* 1. Open Microsoft Excel
'''Steps:'''
* 2. Go to the '''Data''' tab
# Open Microsoft Excel
* 3. Locate the '''Get & Transform Data''' group
# Go to the '''Data''' tab
* 4. Click '''Get Data''' to begin importing
# Locate the '''Get & Transform Data''' group
# Click '''Get Data''' to begin importing


== Hands-on Exercise ==
== Hands-on Exercise: Opening Power Query ==
'''Goal:''' Open Power Query Editor and explore its interface.
'''Goal:''' Open Power Query Editor and explore its interface.
'''File needed:''' [[Media:Sales_January.xlsx|Download Sales_January.xlsx]]


'''Steps:'''
'''Steps:'''
# Open Excel and create a new blank workbook
# Create a new Excel workbook
# Go to the '''Data''' tab → '''Get Data''' → '''From File''' → '''From Workbook...'''
# Go to the '''Data''' tab → '''Get Data''' → '''From File''' → '''From Workbook...'''
# Select the file ''Sales_January.xlsx'' and click ''Import''
# Select the file '''Sales_January.xlsx''' and click '''Import'''
# In the Navigator window, select the worksheet and click '''Transform Data'''
# In the Navigator window, select the worksheet and click '''Transform Data'''
# The Power Query Editor will open
# In the Power Query Editor, explore:
# Review the following interface components:
#* '''Query Pane''' – on the left, showing query name
#* **Query Pane** (left): shows loaded queries (e.g., Sales_January)
#* '''Preview Grid''' – data preview in the middle
#* **Data Preview** (center): preview of your data table
#* '''Applied Steps''' – list of transformations on the right
#* **Applied Steps** (right): list of transformations applied to the data
#* '''Ribbon Tabs''' – Home, Transform, Add Column, View
#* **Ribbon Tabs**: Home, Transform, Add Column, View


== Key Concepts ==
== Additional Exercise: Explore Other Data Sources ==
* '''Query:''' A set of steps used to load and transform data
'''Goal:''' Explore available data sources and create dummy connections.
* '''Applied Steps:''' Each operation is recorded and can be edited or deleted
* '''Load vs. Transform:''' You can either load data directly or open the editor to make changes first


'''Steps:'''
# In Excel → Data tab → '''Get Data'''
# Browse the full list: try connecting to:
#* Web (https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population)
#* Blank Query | Get Data → From Other Sources
#* Recent Sources
# Discuss which sources may be useful in your work.
== Extension: Enable and Explore Formula Bar ==
Power Query has a powerful formula language (M). Let’s enable the formula bar.


'''Steps:'''
# In Power Query Editor, go to the '''View''' tab
# Tick the checkbox for '''Formula Bar'''
# Select a step in the Applied Steps list
# Observe the M code being generated
# Try editing a step manually (e.g., changing a column name)


----
----


Return to [[Power_Query_–_Basic|Main Page]]
Return to [[Power_Query_–_Basic|Main Page]]

Latest revision as of 08:43, 22 June 2025

Module 1: Introduction to Power Query

Objective

Get familiar with the Power Query interface and understand what it can do.

Files Used

What is Power Query?

Power Query is a data connection and transformation tool built into Microsoft Excel. It allows you to import, clean, reshape, and combine data from multiple sources.

You can find Power Query in Excel 2016 and later, under the Data tab in the section called Get & Transform Data.

Where to Find It

Steps:

  1. Open Microsoft Excel
  2. Go to the Data tab
  3. Locate the Get & Transform Data group
  4. Click Get Data to begin importing

Hands-on Exercise: Opening Power Query

Goal: Open Power Query Editor and explore its interface.

Steps:

  1. Create a new Excel workbook
  2. Go to the Data tab → Get Data → From File → From Workbook...
  3. Select the file Sales_January.xlsx and click Import
  4. In the Navigator window, select the worksheet and click Transform Data
  5. In the Power Query Editor, explore:
    • Query Pane – on the left, showing query name
    • Preview Grid – data preview in the middle
    • Applied Steps – list of transformations on the right
    • Ribbon Tabs – Home, Transform, Add Column, View

Additional Exercise: Explore Other Data Sources

Goal: Explore available data sources and create dummy connections.

Steps:

  1. In Excel → Data tab → Get Data
  2. Browse the full list: try connecting to:
  3. Discuss which sources may be useful in your work.

Extension: Enable and Explore Formula Bar

Power Query has a powerful formula language (M). Let’s enable the formula bar.

Steps:

  1. In Power Query Editor, go to the View tab
  2. Tick the checkbox for Formula Bar
  3. Select a step in the Applied Steps list
  4. Observe the M code being generated
  5. Try editing a step manually (e.g., changing a column name)


Return to Main Page

→ Continue to Module 2: Importing and Managing Data.

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