TARGET AUDIENCE
Experienced Excel users wishing to create complex reports, or needing advanced data analytics to process, analyse and display data.
TRAINING OBJECTIVES
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, analyse, and visualize data
COURSE CONTENTS
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
COURSE OUTLINE ::::
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
COURSE OUTLINE ::::
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
COURSE OUTLINE ::::
Programme scope, delivery arrangements and suitability are confirmed during enquiry.