Power Query – Introduction: Difference between revisions

From Training Material
Jump to navigation Jump to search
Created page with "= Module 1: Introduction to Power Query = == What is Power Query? == Power Query is a data connection technology that enables you to discover, connect, combine, and refine data across a wide variety of sources. It is built into Excel (from version 2016 onward) and can be accessed through the **Data** tab under the **Get & Transform Data** section. == Why Use Power Query? == * Automates repetitive data-cleaning tasks * Simplifies combining data from multiple sources * R..."
 
 
(8 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? ==
Power Query is a data connection technology that enables you to discover, connect, combine, and refine data across a wide variety of sources. It is built into Excel (from version 2016 onward) and can be accessed through the **Data** tab under the **Get & Transform Data** section.
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:'''
# Open Microsoft Excel
# Go to the '''Data''' tab
# Locate the '''Get & Transform Data''' group
# Click '''Get Data''' to begin importing


== Why Use Power Query? ==
== Hands-on Exercise: Opening Power Query ==
* Automates repetitive data-cleaning tasks
'''Goal:''' Open Power Query Editor and explore its interface.
* Simplifies combining data from multiple sources
* Reduces the need for manual formulas and copy-pasting
* Improves reliability and traceability of data transformations


== Real-World Examples ==
'''Steps:'''
* Importing monthly sales reports from multiple Excel files and combining them
# Create a new Excel workbook
* Cleaning survey data by removing duplicates and formatting columns
# Go to the '''Data''' tab → '''Get Data''' → '''From File''' → '''From Workbook...'''
* Merging customer data from a CSV file with transaction data from a database
# Select the file '''Sales_January.xlsx''' and click '''Import'''
# In the Navigator window, select the worksheet and click '''Transform Data'''
# 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


== How to Access Power Query in Excel ==
== Additional Exercise: Explore Other Data Sources ==
1. Open Microsoft Excel.
'''Goal:''' Explore available data sources and create dummy connections.
2. Go to the **Data** tab.
3. Locate the **Get & Transform Data** section.


From here, you can:
'''Steps:'''
* Import data from Excel, web, databases, and other formats.
# In Excel → Data tab → '''Get Data'''
* Launch the **Power Query Editor** to shape your 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.


== Hands-on Exercise ==
== Extension: Enable and Explore Formula Bar ==
'''Goal:''' Open Power Query Editor and explore its interface.
Power Query has a powerful formula language (M). Let’s enable the formula bar.


'''Steps:'''
'''Steps:'''
# Open Excel and create a new blank workbook.
# In Power Query Editor, go to the '''View''' tab
# Go to the ''Data'' tab and click on ''Get Data > From Workbook''.
# Tick the checkbox for '''Formula Bar'''
# Select any Excel file and load a sheet with data.
# Select a step in the Applied Steps list
# In the Navigator window, click “Transform Data”.
# Observe the M code being generated
# Explore the Power Query Editor window – look at the applied steps, preview area, and ribbon.
# Try editing a step manually (e.g., changing a column name)
 
----
 
 
Return to [[Power_Query_–_Basic|Main Page]]


== Key Terms ==
→ Continue to [[Power Query – Importing and Managing Data|Module 2: Importing and Managing Data]].
* '''Query''' – A set of steps that transform your data
* '''Applied Steps''' – The list of transformations done on the data
* '''Data Source''' – Where the original data comes from (Excel file, database, etc.)


== Recap ==
* [[Power Query – Introduction|Module 1: Introduction to Power Query]]
In this module, you learned what Power Query is, how to access it, and why it's useful for data preparation and automation.
* [[Power Query – Importing and Managing Data|Module 2: Importing and Managing Data]]
* [[Power Query – Data Transformation|Module 3: Data Transformation Techniques]]
* [[Power Query – Automation and Parameters|Module 4: Automation and Parameters]]
* [[Power Query – Power Pivot Overview|Module 5: Power Pivot Overview]]
* [[Power Query – Summary and Exercises|Module 6: Summary and Final Exercises]]

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"