Duration
1 days
What you’ll learn in this course
The Excel Power Query gets data from almost anywhere (web sites, databases, Excel files, SharePoint, Salesforce etc.) and enables you to manipulate it in many ways (clean, transform, merge and append) using a straightforward interface add-in to Microsoft Excel. Non-technical Excel users can access large corporate databases to conduct analysis and produce reports.
How you’ll benefit
At the end of this course, delegates will be able to:
- Create effective and professional reports
- Gather and transform data from multiple sources
- Discover and combine data in mashups
- Learn about data model creation
- Explore, analyze, and visualize data
Who should enroll
Experienced Excel users wishing to create complex reports, or needing advanced data analytics to process, analyze and display data.
Course Outline
9:00am-10:30am
Introduction to Power Query
- Installing Power Query
- Create and Edit a simple query
Common Data Import Sources
- Working with CSV; Text; Excel Files
- Importing multiple files
- Working with Web data
- Scraping Data from Web Pages
- Calling a Web Service
- Finding Data using a Power Query Online Search
Working with Folders and Multiple Files
- Using data from Windows File manager
- Combining Data from Multiple Files
10:30am-10:45pm Morning Break
10:45pm-12:30pm
Working with Columns
- Name; Move; Split; Merge
Filtering and Sorting
- Using Auto-Filter
- Using Number, Text and Date Filters
- Filtering Rows by Range
- Removing Duplicate Values
- Filtering out Rows with Errors
- Sorting
- Grouping rows
Changing Values in a Table
- Replacing Values
- Transformations: – Text; Number; Date/Time
- Replacing Missing Values
12:30pm-1:30pm Lunch
1:30pm-3:30pm
Table Transformations
- Un-pivoting Columns to Rows
- Transposing a Table
- Creating Custom Columns
Loading Data
- Loading Data into a Worksheet
- Loading Data into the Excel Data Model
- Power Query and Table Relationships
- Refreshing Queries Manual & Auto
3:30pm-3:45pm Afternoon Break
3:45pm-5:00pm
Query Editor
- Edit Query Steps
- Edit Query Step Settings
- Refresh a Query
Queries
- Understanding Power Query’s language syntax
- Merging tables and queries
- Using Power Query functions in columns
- Using conditional (IF) statements
- Creating custom Power Query functions
- Implementing dynamic parameter tables
- Creating calendar tables
- Sharing queries
- Best practices for Query organization
Programme scope, delivery arrangements and suitability are confirmed during enquiry.